Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 16E5E47635D for ; Wed, 22 Jan 2003 18:41:43 -0500 (EST) Received: from jester.senspire.com (CPE00508b028d7d-CM00803785c5e0.cpe.net.cable.rogers.com [24.103.51.175]) by postgresql.org (Postfix) with ESMTP id 216C7476A0E for ; Wed, 22 Jan 2003 17:54:33 -0500 (EST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by jester.senspire.com (8.12.6/8.12.6) with ESMTP id h0MMsKYJ059528; Wed, 22 Jan 2003 17:54:21 -0500 (EST) (envelope-from rbt@rbt.ca) Subject: Re: Slow query on OS X box From: Rod Taylor To: Patrick Hatcher Cc: Postgresql Performance In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-Ddx8v7ZEOD45HkgRKSwg" Organization: Message-Id: <1043276059.83856.180.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 22 Jan 2003 17:54:20 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/233 X-Sequence-Number: 880 --=-Ddx8v7ZEOD45HkgRKSwg Content-Type: text/plain Content-Transfer-Encoding: quoted-printable 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. :( >=20 > 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. >=20 > I guess the bigger issue is that when trying to do aggregates, grouping by > the wizard field, it takes just as long. >=20 > Ex: > mdc_oz=3D# explain analyze select wizard,count(wizard) from search_log wh= ere > sdate > between '2002-12-01' and '2003-01-15' group by wizard; > QUERY > PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------- > Aggregate (cost=3D1083300.43..1112411.55 rows=3D388148 width=3D10) (act= ual > time=3D229503.85..302617.75 rows=3D14 loops=3D1) > -> Group (cost=3D1083300.43..1102707.84 rows=3D3881482 width=3D10) (= actual > time=3D229503.60..286014.83 rows=3D3717161 loops=3D1) > -> Sort (cost=3D1083300.43..1093004.14 rows=3D3881482 width=3D= 10) > (actual time=3D229503.57..248415.81 rows=3D3717161 loops=3D1) > Sort Key: wizard > -> Seq Scan on search_log (cost=3D0.00..575217.57 > rows=3D3881482 width=3D10) (actual time=3D91235.76..157559.58 rows=3D3717= 161 > loops=3D1) > Filter: ((sdate >=3D '2002-12-01'::date) AND (sdate > <=3D '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=3D# explain analyze select wizard from search_log where wizard > > =3D'Keyword' and sdate between '2002-12-01' and '2003-01-15'; > > QUERY PLAN > > > -------------------------------------------------------------------------= ---------------------------------------------------- >=20 > > Seq Scan on search_log (cost=3D0.00..609015.34 rows=3D3305729 width= =3D10) > > (actual time=3D99833.83..162951.25 rows=3D3280573 loops=3D1) > > Filter: ((wizard =3D 'Keyword'::character varying) AND (sdate > > > =3D '2002-12-01'::date) AND (sdate <=3D '2003-01-15'::date)) > > Total runtime: 174713.25 msec > > (3 rows) > -- > Rod Taylor >=20 > PGP Key: http://www.rbt.ca/rbtpub.asc > (See attached file: signature.asc) >=20 >=20 > ______________________________________________________________________ >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-Ddx8v7ZEOD45HkgRKSwg Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) iD8DBQA+LyEa6DETLow6vwwRAozBAJ0UfoOxkmyVKfFX/mk5EQWGhzx9WgCdGPOl BFCJvs11Hd2cujy6g5jrfBg= =ZU4w -----END PGP SIGNATURE----- --=-Ddx8v7ZEOD45HkgRKSwg--