From: Reiner Dassing <dassing@wettzell.ifag.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Index of a table is not used (in any case)
Date: Thu, 25 Oct 2001 14:52:48 +0200
Message-ID: <3BD80B20.F0C687CD@wettzell.ifag.de> (raw)
References: <3BD3BFE0.55FC3EC0@wettzell.ifag.de>
<6446.1003864454@sss.pgh.pa.us>
Hello Tom!
Tom Lane wrote:
>
> 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?
>
Well, I did not claim that i made a VACUUM ANALYZE, I just set up a new
table
for testing purposes doing just INSERTs.
After VACUUM ANALYSE the results look like:
explain select * from wetter where epoche between '1970-01-01' and
test_wetter-# '1980-01-01' order by epoche asc;
NOTICE: QUERY PLAN:
Index Scan using wetter_epoche_idx on wetter (cost=0.00..3313780.74
rows=20319660 width=16)
EXPLAIN
Now, the INDEX Scan is used and therefore, the query is very fast, as
expected.
For me, as a user not being involved in all the intrinsics of
PostgreSQL, the question was
"Why is this SELECT so slow?" (this question is asked a lot of times in
this Mail lists)
Now, I would like to say thank you! You have explained me and hopefully
many more users
what is going on behind the scene.
> 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
--
Mit freundlichen Gruessen / With best regards
Reiner Dassing
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: dassing@wettzell.ifag.de, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Index of a table is not used (in any case)
In-Reply-To: <3BD80B20.F0C687CD@wettzell.ifag.de>
* 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