Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2696E477117; Wed, 22 Jan 2003 19:30:30 -0500 (EST) Received: from fddlnint05.fds.com (fddlnint05.fds.com [208.15.91.52]) by postgresql.org (Postfix) with ESMTP id 829BE47630A; Wed, 22 Jan 2003 19:10:11 -0500 (EST) Subject: Re: Slow query on OS X box To: "Rod Taylor Cc: Postgresql Performance , pgsql-performance-owner@postgresql.org X-Mailer: Lotus Notes Release 5.0.8 June 18, 2001 Message-ID: From: "Patrick Hatcher" Date: Wed, 22 Jan 2003 16:05:39 -0800 X-MIMETrack: Serialize by Router on FDDLNINT05/FSG/SVR/FDD(Release 5.0.4 |June 8, 2000) at 01/22/2003 07:07:12 PM MIME-Version: 1.0 Content-type: multipart/mixed; Boundary="0__=88256CB7000066218f9e8a93df938690918c88256CB700006621" Content-Disposition: inline X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/241 X-Sequence-Number: 888 --0__=88256CB7000066218f9e8a93df938690918c88256CB700006621 Content-type: text/plain; charset=us-ascii Thanks everyone. I'll give your suggestions a try and report back. Patrick Hatcher Macys.Com Legacy Integration Developer 415-422-1610 office HatcherPT - AIM Rod Taylor Sent by: To: Patrick Hatcher pgsql-performance-owner@post cc: Postgresql Performance gresql.org Subject: Re: [PERFORM] Slow query on OS X box 01/22/2003 02:54 PM Yup, since you still need to pull everything off the disk (the slowest part), which is quite a bit of data. You're simply dealing with a lot of data for a single query -- not much you can do. Is this a dedicated -- one client doing big selects like this? Knock your shared_buffers down to about 2000, bump your sort mem up to around 32MB (128MB or so if it's a dedicated box with a vast majority of queries like the below). Okay, need to do something about the rest of the data. 13million * 2k is a big number. Do you have a set of columns that are rarely used? If so, toss them into a separate table and link via a unique identifier (int4). It'll cost extra when you do hit them, but pulling out a few of the large ones information wise would buy quite a bit. Now, wizard. For that particular query it would be best if entries were made for all the values of wizard into a lookup table, and change search_log.wizard into a reference to that entry in the lookup. Index the lookup table well (one in the wizard primary key -- int4, and a unique index on the 'wizard' varchar). Group by the number, join to the lookup table for the name. Any other values with highly repetitive data? Might want to consider doing the same for them. In search_log, index the numeric representation of 'wizard' (key from lookup table), but don't bother indexing numbers that occur regularly. Look up how to create a partial index. Ie. The value 'Keyword' could be skipped as it occurs once in four tuples -- too often for an index to be useful. On Wed, 2003-01-22 at 15:49, Patrick Hatcher wrote: > 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) > 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? > > 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) > > > ______________________________________________________________________ > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc (See attached file: signature.asc) --0__=88256CB7000066218f9e8a93df938690918c88256CB700006621 Content-type: application/octet-stream; name="signature.asc" Content-Disposition: attachment; filename="signature.asc" Content-transfer-encoding: base64 LS0tLS1CRUdJTiBQR1AgU0lHTkFUVVJFLS0tLS0NClZlcnNpb246IEdudVBH IHYxLjIuMSAoRnJlZUJTRCkNCg0KaUQ4REJRQStMeUVhNkRFVExvdzZ2d3dS QW96QkFKMFVmb094a215VktmRlgvbWs1RVFXR2h6eDlXZ0NkR1BPbA0KQkZD SnZzMTFIZDJjdWp5Nmc1anJmQmc9DQo9WlU0dw0KLS0tLS1FTkQgUEdQIFNJ R05BVFVSRS0tLS0tDQo= --0__=88256CB7000066218f9e8a93df938690918c88256CB700006621--