From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Kalchev <daniel@digsys.bg>
Cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage
Date: Wed, 09 Jan 2002 10:14:05 -0500
Message-ID: <7235.1010589245@sss.pgh.pa.us> (raw)
In-Reply-To: <200201090745.JAA15141@dcave.digsys.bg>
References: <200201090745.JAA15141@dcave.digsys.bg>
Daniel Kalchev <daniel@digsys.bg> writes:
> Aggregate (cost=47721.72..47721.72 rows=1 width=8)
> -> Seq Scan on iplog_gate200112 (cost=0.00..47579.54 rows=56873 width=
> 8)
>>>
> If you say "set enable_seqscan to off", does that change the plan?
>>>
> Aggregate (cost=100359.71..100359.71 rows=1 width=8)
> -> Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112
> (cost=0.00..100217.52 rows=56873 width=8)
>>>
>>> So, what we've got here is a difference of opinion: the planner thinks
>>> that the seqscan will be faster. How many rows are actually selected
>>> by this WHERE clause? How long does each plan actually take?
> 3-5 minutes with sequential scan; 10-15 sec with index scan. The query returns
> 4062 rows. Out of ca 1700000 rows.
> With only the datetime constraints (which relates to the index), the number of
> rows is 51764.
Hm. Okay, so the number-of-rows estimate is not too far off. I concur
with Hiroshi's comment: the reason the indexscan is so fast must be that
the table is clustered (physical order largely agrees with index order).
This would obviously hold if the records were entered in order by
ipdate; is that true?
The 7.2 planner does try to estimate index order correlation, and would
be likely to price this indexscan much lower, so that it would make the
right choice. I'd suggest updating to 7.2 as soon as we have RC1 out.
(Don't do it yet, though, since we've got some timestamp bugs to fix
that are probably going to require another initdb.)
> In any case, sequential scan of millions of rows cannot be faster than index
> scan.
Snort. If that were true, we could get rid of most of the complexity
in the planner.
regards, tom lane
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-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, daniel@digsys.bg
Subject: Re: again on index usage
In-Reply-To: <7235.1010589245@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