Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1A5729FB39C for ; Thu, 4 Oct 2007 16:52:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 31064-06 for ; Thu, 4 Oct 2007 16:52:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from newmail.silentmedia.com (earth.silentmedia.com [216.254.19.12]) by postgresql.org (Postfix) with ESMTP id 04A8C9FB249 for ; Thu, 4 Oct 2007 16:52:41 -0300 (ADT) Received: by newmail.silentmedia.com (Postfix, from userid 99) id 3611435E3C5; Thu, 4 Oct 2007 12:52:38 -0700 (PDT) Received: from GRD (GRD [10.125.42.6]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by newmail.silentmedia.com (Postfix) with ESMTP id F002835E3BF; Thu, 4 Oct 2007 12:52:36 -0700 (PDT) Date: Thu, 4 Oct 2007 12:52:36 -0700 (PDT) From: Ben X-X-Sender: bench@localhost.localdomain To: Tom Lane cc: pgsql-performance@postgresql.org Subject: Re: quickly getting the top N rows In-Reply-To: <3785.1191523930@sss.pgh.pa.us> Message-ID: References: <20071004141411.9509fe66.wmoran@collaborativefusion.com> <3785.1191523930@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/43 X-Sequence-Number: 27065 On Thu, 4 Oct 2007, Tom Lane wrote: > You're being about as clear as mud here, except that you obviously lied > about what you were doing in your first message. If you have a planner > problem, show us the *exact* query, the *exact* table definition, and > unfaked EXPLAIN ANALYZE output. I didn't realize that simplification was viewed as so sinister, but thanks, I'll remember that in the future. The table: Table "public.log" Column | Type | Modifiers ----------------+-----------------------------+--------------------- clientkey | character(30) | not null premiseskey | character(30) | not null logkey | character(30) | not null logicaldel | character(1) | default 'N'::bpchar lockey | character(30) | devlockey | character(30) | eventkey | character(30) | logshorttext | character varying(255) | logdesc | character varying(255) | loguserkey | character(30) | logassetkey | character(30) | logresourcekey | character(30) | logtime | timestamp without time zone | logip | character varying(50) | logarchived | character(1) | logarchivedate | timestamp without time zone | loghasvideo | character(1) | loghasaudio | character(1) | resvehiclekey | character(30) | synccreated | character(1) | logtypekey | character(30) | Indexes: "log_pkey" PRIMARY KEY, btree (clientkey, premiseskey, logkey) "eventkey_idx" btree (eventkey), "log_ak1" btree (clientkey, premiseskey, logtime, logkey) The original, slow query: explain analyze SELECT * FROM log WHERE clientkey in ('000000004500000000010000000001') AND premiseskey in ('000000004500000000010000000001') and logicaldel = 'N' ORDER BY logtime desc, logkey desc, clientkey desc, premiseskey desc LIMIT 20 offset 0; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=356402.58..356402.63 rows=20 width=563) (actual time=215858.481..215858.527 rows=20 loops=1) -> Sort (cost=356402.58..357598.25 rows=478267 width=563) (actual time=215858.478..215858.498 rows=20 loops=1) Sort Key: logtime, logkey, clientkey, premiseskey -> Seq Scan on log (cost=0.00..52061.67 rows=478267 width=563) (actual time=29.340..100043.313 rows=475669 loops=1) Filter: ((clientkey = '000000004500000000010000000001'::bpchar) AND (premiseskey = '000000004500000000010000000001'::bpchar) AND (logicaldel = 'N'::bpchar)) Total runtime: 262462.582 ms (6 rows) Every row in log has identical clientkey and premiseskey values, so if I just remove those columns from the order by clause, I get this far superior plan: explain analyze SELECT * FROM log WHERE clientkey in ('000000004500000000010000000001') AND premiseskey in ('000000004500000000010000000001') and logicaldel = 'N' ORDER BY logtime desc, logkey desc LIMIT 20 offset 0; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..12.33 rows=20 width=563) (actual time=0.047..0.105 rows=20 loops=1) -> Index Scan Backward using log_ak1 on log (cost=0.00..294735.70 rows=478267 width=563) (actual time=0.044..0.076 rows=20 loops=1) Index Cond: ((clientkey = '000000004500000000010000000001'::bpchar) AND (premiseskey = '000000004500000000010000000001'::bpchar)) Filter: (logicaldel = 'N'::bpchar) Total runtime: 0.165 ms (5 rows) ...which made me to think that maybe postgres is not using log_ak1 in the former case because two of the columns in the order by match every row. Unfortunately, in this case it's not an option to alter the query. I'm just trying to figure out an explaination.