Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AE9A4476FF4 for ; Wed, 22 Jan 2003 16:10:23 -0500 (EST) Received: from lakemtao01.cox.net (lakemtao01.cox.net [68.1.17.244]) by postgresql.org (Postfix) with ESMTP id 5BDAA476FFA for ; Wed, 22 Jan 2003 15:35:27 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao01.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030122203528.CDZB16369.lakemtao01.cox.net@[192.168.1.1]> for ; Wed, 22 Jan 2003 15:35:28 -0500 Subject: Re: Slow query on OS X box From: Ron Johnson To: PgSQL Performance ML In-Reply-To: References: Content-Type: text/plain Organization: Message-Id: <1043267721.22135.141.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 22 Jan 2003 14:35:22 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/228 X-Sequence-Number: 875 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.l.johnson@cox.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 | +---------------------------------------------------------------+