pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Patrick Hatcher <PHatcher@macys.com>
To: pgsql-performance@postgresql.org
Subject: Slow query on OS X box
Date: Wed, 22 Jan 2003 10:26:17 -0800
Message-ID: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com> (raw)

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);




view thread (8+ messages)  latest in thread

Message-ID: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>
Permalink:  ../OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com/
Also on:    postgresql.org/message-id/OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com

 · 

reply

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: PHatcher@macys.com
  Subject: Re: Slow query on OS X box
  In-Reply-To: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>
  References: <OF5B777E26.43FD52D5-ON88256CB6.00635521@fds.com>

* 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