Skip site navigation (1) Skip section navigation (2)

Re: query plan wierdness?

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Joel McGraw <jmcgraw(at)eldocomp(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: query plan wierdness?
Date: 2004-07-07 22:50:31
Message-ID: 20040707154849.J63760@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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?

In response to

pgsql-performance by date

Next:From: Shridhar DaithankarDate: 2004-07-08 09:02:43
Subject: Re: inserting into brand new database faster than old database
Previous:From: Joel McGrawDate: 2004-07-07 22:12:29
Subject: Re: query plan wierdness?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group