Re: Slow query on OS X box
От | Tomasz Myrta |
---|---|
Тема | Re: Slow query on OS X box |
Дата | |
Msg-id | 3E2F060E.9000509@klaster.net обсуждение исходный текст |
Ответ на | Slow query on OS X box ("Patrick Hatcher" <PHatcher@macys.com>) |
Список | pgsql-performance |
Patrick Hatcher wrote: >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); Did you try to change theses 2 indexes into 1? CREATE INDEX date_wizard_idx on search_log USING btree(wizard,sdate) How selective are these fields: - if you ask about wizard="Keyword", the answer is 0.1% or 5% or 50% of rows? - if you ask about sdate >= '2002-12-01'::date) AND (sdate <= '2003-01-15'::date) what is the answer? Consider creating table "wizards", and changing field "wizard" in table "search_log" into integer field "wizardid". Searching by integer is faster than by varchar. Regards, Tomasz Myrta
В списке pgsql-performance по дате отправления: