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 по дате отправления:

Предыдущее
От: "Patrick Hatcher"
Дата:
Сообщение: Re: Slow query on OS X box
Следующее
От: John Lange
Дата:
Сообщение: Query plan and Inheritance. Weird behavior