From: Tomasz Myrta <jasiek@klaster.net>
To: Patrick Hatcher <PHatcher@macys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query on OS X box
Date: Wed, 22 Jan 2003 21:58:54 +0100
Message-ID: <3E2F060E.9000509@klaster.net> (raw)
In-Reply-To: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>
References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: jasiek@klaster.net, PHatcher@macys.com
Subject: Re: Slow query on OS X box
In-Reply-To: <3E2F060E.9000509@klaster.net>
References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com> <3E2F060E.9000509@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox