pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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?



view thread (9+ messages)  latest in thread

Message-ID: <20040707154849.J63760@megazone.bigpanda.com>
Permalink:  ../20040707154849.J63760@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20040707154849.J63760@megazone.bigpanda.com

 · 

reply

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