From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Joel McGraw <jmcgraw@eldocomp.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query plan wierdness?
Date: Wed, 7 Jul 2004 15:50:31 -0700 (PDT)
Message-ID: <20040707154849.J63760@megazone.bigpanda.com> (raw)
In-Reply-To: <7B3E33EF2A10A84185E3667F6B9A1B781A0679@ECIEXCHANGE.eldocomp.com>
References: <7B3E33EF2A10A84185E3667F6B9A1B781A0679@ECIEXCHANGE.eldocomp.com>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, jmcgraw@eldocomp.com
Subject: Re: query plan wierdness?
In-Reply-To: <20040707154849.J63760@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox