Re: Slow query on OS X box

What about creating a multi-segment index on wizard/sdate?

On a side note: that record is ~8KB long, which is kinda big. You could
split those column into a seperate table (or tables), so that when you
want to query, say, gender, department & trip_length, you won't have to
read in *so*much* extra data, slowing the query down.

Also, these column sizes seem kind excessive, and allow for bad data to
seep in to the table:
timestamp varchar(75),
age varchar(25),
metal varchar(255),
gem varchar(255),
bra_size varchar(25),

On Wed, 2003-01-22 at 12:26, 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);
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
+---------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "My advice to you is to get married: If you find a good wife, |
| you will be happy; if not, you will become a philosopher." |
| Socrates |
+---------------------------------------------------------------+