From: | "Patrick Hatcher" <PHatcher(at)macys(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Slow query on OS X box |
Date: | 2003-01-22 18:26:17 |
Message-ID: | OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I have a table that contains over 13 million rows. This query takes an
extremely long time to return. I've vacuum full, analyzed, and re-indexed
the table. Still the results are the same. Any ideas?
TIA
Patrick
mdc_oz=# explain analyze select wizard from search_log where wizard
='Keyword' and sdate between '2002-12-01' and '2003-01-15';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on search_log (cost=0.00..609015.34 rows=3305729 width=10)
(actual time=99833.83..162951.25 rows=3280573 loops=1)
Filter: ((wizard = 'Keyword'::character varying) AND (sdate >
= '2002-12-01'::date) AND (sdate <= '2003-01-15'::date))
Total runtime: 174713.25 msec
(3 rows)
My box I'm running PG on:
Dual 500 Mac OS X
1g ram
Pg 7.3.0
Conf settings
max_connections = 200
shared_buffers = 15200
#max_fsm_relations = 100 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4, typically 8KB each
CREATE TABLE public.search_log (
wizard varchar(50) NOT NULL,
sub_wizard varchar(50),
timestamp varchar(75),
department int4,
gender varchar(25),
occasion varchar(50),
age varchar(25),
product_type varchar(2000),
price_range varchar(1000),
brand varchar(2000),
keyword varchar(1000),
result_count int4,
html_count int4,
fragrance_type varchar(50),
frag_type varchar(50),
frag_gender char(1),
trip_length varchar(25),
carry_on varchar(25),
suiter varchar(25),
expandable varchar(25),
wheels varchar(25),
style varchar(1000),
heel_type varchar(25),
option varchar(50),
metal varchar(255),
gem varchar(255),
bra_size varchar(25),
feature1 varchar(50),
feature2 varchar(50),
feature3 varchar(50),
sdate date,
stimestamp timestamptz,
file_name text
) WITH OIDS;
CREATE INDEX date_idx ON search_log USING btree (sdate);
CREATE INDEX slog_wizard_idx ON search_log USING btree (wizard);
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-01-22 18:57:29 | Re: Performance between triggers/functions written in C and PL/PGSQL |
Previous Message | Seth Robertson | 2003-01-22 17:45:24 | Re: Postgres 7.3.1 poor insert/update/search performance |