X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2F2BCD1B20D for ; Wed, 7 Jul 2004 19:50:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85909-10 for ; Wed, 7 Jul 2004 22:50:36 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id ED521D1B23F for ; Wed, 7 Jul 2004 19:50:31 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 96EF4358D8; Wed, 7 Jul 2004 15:50:31 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 95669358D2; Wed, 7 Jul 2004 15:50:31 -0700 (PDT) Date: Wed, 7 Jul 2004 15:50:31 -0700 (PDT) From: Stephan Szabo To: Joel McGraw Cc: "pgsql-performance@postgresql.org" Subject: Re: query plan wierdness? In-Reply-To: <7B3E33EF2A10A84185E3667F6B9A1B781A0679@ECIEXCHANGE.eldocomp.com> Message-ID: <20040707154849.J63760@megazone.bigpanda.com> References: <7B3E33EF2A10A84185E3667F6B9A1B781A0679@ECIEXCHANGE.eldocomp.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/31 X-Sequence-Number: 7422 On Wed, 7 Jul 2004, Joel McGraw wrote: > However, this query performs a sequence scan on the table, ignoring the > call_idx13 index (the only difference is the addition of the aspid field > in the order by clause): > > elon2=# explain analyse select * from call where aspid='123C' and > OpenedDateTime between '2000-01-01 00:00:00.0' and '2004-06-24 > 23:59:59.999' order by aspid, openeddatetime desc, callstatus desc, > calltype desc, callkey desc limit 26; > > QUERY PLAN > > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------ > Limit (cost=349379.41..349379.48 rows=26 width=297) (actual > time=32943.52..32943.61 rows=26 loops=1) > -> Sort (cost=349379.41..350558.87 rows=471781 width=297) (actual > time=32943.52..32943.56 rows=27 loops=1) > Sort Key: aspid, openeddatetime, callstatus, calltype, callkey > -> Seq Scan on call (cost=0.00..31019.36 rows=471781 > width=297) (actual time=1.81..7318.13 rows=461973 loops=1) > Filter: ((aspid = '123C'::bpchar) AND (openeddatetime >= > '2000-01-01 00:00:00-07'::timestamp with time zone) AND (openeddatetime > <= '2004-06-24 23:59:59.999-07'::timestamp with time zone)) > Total runtime: 39353.86 msec > (6 rows) Hmm, what does it say after a set enable_seqscan=off? Also, what does it say if you use aspid desc rather than just aspid in the order by?