Received: from gate-w.wettzell.ifag.de (gate-w.ifag.de [141.74.1.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9PCqxP64723; Thu, 25 Oct 2001 08:52:59 -0400 (EDT) (envelope-from dassing@wettzell.ifag.de) Received: (from root@localhost) by gate-w.wettzell.ifag.de (8.9.3/8.9.3) id OAA20186; Thu, 25 Oct 2001 14:52:52 +0200 (CEST) Received: (from localhost) by gate-w.wettzell.ifag.de (MSCAN) id 2/gate-w.wettzell.ifag.de/smtp-gw/mscan; Thu Oct 25 14:52:52 2001 Message-ID: <3BD80B20.F0C687CD@wettzell.ifag.de> Date: Thu, 25 Oct 2001 14:52:48 +0200 From: Reiner Dassing Organization: BKG-Wettzell X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U) X-Accept-Language: de,en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Index of a table is not used (in any case) References: <3BD3BFE0.55FC3EC0@wettzell.ifag.de> <6446.1003864454@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200110/355 X-Sequence-Number: 5308 Hello Tom! Tom Lane wrote: > > Reiner Dassing 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