Slow query on OS X box
От | Patrick Hatcher |
---|---|
Тема | Slow query on OS X box |
Дата | |
Msg-id | OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com обсуждение исходный текст |
Ответы |
Re: Slow query on OS X box
Re: Slow query on OS X box Re: Slow query on OS X box Re: Slow query on OS X box |
Список | 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);
В списке pgsql-performance по дате отправления: