Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3F211476EF0 for ; Wed, 22 Jan 2003 13:56:46 -0500 (EST) Received: from fddlnint05.fds.com (fddlnint05.fds.com [208.15.91.52]) by postgresql.org (Postfix) with ESMTP id 38810476F09 for ; Wed, 22 Jan 2003 13:30:42 -0500 (EST) Subject: Slow query on OS X box To: pgsql-performance@postgresql.org X-Mailer: Lotus Notes Release 5.0.8 June 18, 2001 Message-ID: From: "Patrick Hatcher" Date: Wed, 22 Jan 2003 10:26:17 -0800 X-MIMETrack: Serialize by Router on FDDLNINT05/FSG/SVR/FDD(Release 5.0.4 |June 8, 2000) at 01/22/2003 01:27:40 PM MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/225 X-Sequence-Number: 872 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);