pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Reiner Dassing <dassing@wettzell.ifag.de>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: Index of a table is not used (in any case)
Date: Tue, 23 Oct 2001 15:14:14 -0400
Message-ID: <6446.1003864454@sss.pgh.pa.us> (raw)
In-Reply-To: <3BD3BFE0.55FC3EC0@wettzell.ifag.de>
References: <3BD3BFE0.55FC3EC0@wettzell.ifag.de>

Reiner Dassing <dassing@wettzell.ifag.de> writes:
> explain select * from wetter order by epoche desc;    
> NOTICE:  QUERY PLAN:

> Index Scan Backward using wetter_epoche_idx on wetter 
> (cost=0.00..3216018.59 rows=20340000 width=16)

> explain select * from wetter where epoche between '1970-01-01' and
> '1980-01-01' order by epoche asc;
> NOTICE:  QUERY PLAN:

> Sort  (cost=480705.74..480705.74 rows=203400 width=16)
>   ->  Seq Scan on wetter  (cost=0.00..454852.00 rows=203400 width=16)

It's hard to believe that you've done a VACUUM ANALYZE on this table,
since you are getting a selectivity estimate of exactly 0.01, which
just happens to be the default selectivity estimate for range queries.
How many rows are there really in this date range?

Anyway, the reason the planner is picking a seqscan+sort is that it
thinks that will be faster than an indexscan.  It's not necessarily
wrong.  Have you compared the explain output and actual timings both
ways?  (Use "set enable_seqscan to off" to force it to pick an indexscan
for testing purposes.)

			regards, tom lane



view thread (11+ messages)  latest in thread

Message-ID: <6446.1003864454@sss.pgh.pa.us>
Permalink:  ../6446.1003864454@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/6446.1003864454@sss.pgh.pa.us

 · 

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-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dassing@wettzell.ifag.de, pgsql-hackers@postgresql.org
  Subject: Re: Index of a table is not used (in any case)
  In-Reply-To: <6446.1003864454@sss.pgh.pa.us>

* 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