From: Daniel Kalchev <daniel@digsys.bg>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage
Date: Wed, 09 Jan 2002 09:45:51 +0200
Message-ID: <200201090745.JAA15141@dcave.digsys.bg> (raw)
In-Reply-To: <23405.1010500631@sss.pgh.pa.us>
>>>Tom Lane said:
> Daniel Kalchev <daniel@digsys.bg> writes:
> > Same result (sorry, should have included this originally):
>
> > 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?
>
> regards, tom lane
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.
In any case, sequential scan of millions of rows cannot be faster than index
scan. The average number of records for each index key is around 25000 -
perhaps the planner thinks because the number of tuples in this case is
higher, it should prefer sequential scan. I guess the planner will do better
if there is some scaling of these values with respect to the total number of
rows.
Daniel
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: daniel@digsys.bg, tgl@sss.pgh.pa.us
Subject: Re: again on index usage
In-Reply-To: <200201090745.JAA15141@dcave.digsys.bg>
* 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