Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1B8C5475D64 for ; Wed, 22 Jan 2003 16:25:53 -0500 (EST) Received: from fddlnint05.fds.com (fddlnint05.fds.com [208.15.91.52]) by postgresql.org (Postfix) with ESMTP id D80604766F8 for ; Wed, 22 Jan 2003 15:54:11 -0500 (EST) Subject: Re: Slow query on OS X box To: "Rod Taylor Cc: Postgresql Performance X-Mailer: Lotus Notes Release 5.0.8 June 18, 2001 Message-ID: From: "Patrick Hatcher" Date: Wed, 22 Jan 2003 12:49:49 -0800 X-MIMETrack: Serialize by Router on FDDLNINT05/FSG/SVR/FDD(Release 5.0.4 |June 8, 2000) at 01/22/2003 03:51:11 PM MIME-Version: 1.0 Content-type: multipart/mixed; Boundary="0__=88256CB6006EA4498f9e8a93df938690918c88256CB6006EA449" Content-Disposition: inline X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/229 X-Sequence-Number: 876 --0__=88256CB6006EA4498f9e8a93df938690918c88256CB6006EA449 Content-type: text/plain; charset=us-ascii Sorry I'm being really dense today. I didn't even notice the 3.2 million row being returned. :( To answer your question, no, all fields would not have data. The data we receive is from a Web log file. It's parsed and then uploaded to this table. I guess the bigger issue is that when trying to do aggregates, grouping by the wizard field, it takes just as long. Ex: mdc_oz=# explain analyze select wizard,count(wizard) from search_log where sdate between '2002-12-01' and '2003-01-15' group by wizard; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1083300.43..1112411.55 rows=388148 width=10) (actual time=229503.85..302617.75 rows=14 loops=1) -> Group (cost=1083300.43..1102707.84 rows=3881482 width=10) (actual time=229503.60..286014.83 rows=3717161 loops=1) -> Sort (cost=1083300.43..1093004.14 rows=3881482 width=10) (actual time=229503.57..248415.81 rows=3717161 loops=1) Sort Key: wizard -> Seq Scan on search_log (cost=0.00..575217.57 rows=3881482 width=10) (actual time=91235.76..157559.58 rows=3717161 loops=1) Filter: ((sdate >= '2002-12-01'::date) AND (sdate <= '2003-01-15'::date)) Total runtime: 302712.48 msec (7 rows) Thanks again for the help Patrick Hatcher Rod Taylor To: Patrick Hatcher cc: Postgresql Performance 01/22/2003 Subject: Re: [PERFORM] Slow query on OS X box 12:02 PM On Wed, 2003-01-22 at 13: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? Yeah, you're pulling out 3.2 million rows from (possibly) a wide table bytewise. Do all of those fields actually have data? Thats always going to take a while -- and I find it hard to believe you're actually doing something with all of those rows that runs regularly. If every one of those rows was maxed out (ignoring the text field at the end) you could have ~ 15GB of data to pull out. Without knowing the type of data actually in the table, I'm going to bet it's a harddrive limitation. The index on 'wizard' is next to useless as at least 1/4 of the data in the table is under the same key. You might try a partial index on 'wizard' (skip the value 'Keyword'). It won't help this query, but it'll help ones looking for values other than 'Keyword'. Anyway, you might try a CURSOR. Fetch rows out 5000 at a time, do some work with them, then grab some more. This -- more or less -- will allow you to process the rows received while awaiting the remaining lines to be processed by the database. Depending on what you're doing with them it'll give a chance for the diskdrive to catch up. If the kernels smart it'll read ahead of the scan. This doesn't remove read time, but hides it while you're transferring the data out (from the db to your client) or processing it. > 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) -- Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc (See attached file: signature.asc) --0__=88256CB6006EA4498f9e8a93df938690918c88256CB6006EA449 Content-type: application/octet-stream; name="signature.asc" Content-Disposition: attachment; filename="signature.asc" Content-transfer-encoding: base64 LS0tLS1CRUdJTiBQR1AgU0lHTkFUVVJFLS0tLS0NClZlcnNpb246IEdudVBH IHYxLjIuMSAoRnJlZUJTRCkNCg0KaUQ4REJRQStMdmk3NkRFVExvdzZ2d3dS QWszakFKNGdGaWVxVStVemdCUXBwbUx4OUhoYnAwQTFud0NnZ3VkMA0KYlMv bGNJNG5UYzhPSFFwVFZRK1lCNjA9DQo9cFN2Rg0KLS0tLS1FTkQgUEdQIFNJ R05BVFVSRS0tLS0tDQo= --0__=88256CB6006EA4498f9e8a93df938690918c88256CB6006EA449--