I expected Postgresql to use an indexed access method, but in certain cases
it is using a sequential scan. Details are below:
sobjid int8 primary key,
v int8 not null,
ord int2 not null)
* The table contains 1.1 million rows.
* Column 'v' exhibits very high selectivity: "select count(*) from (select
distinct v from p1_nrn_road) A" returns 1,051,276.
* The table contains one index: P1_NRN_ROAD_V (v, sobjid) (The index
includes the column sobjid because the query projects this col, and its
inclusion in the index allows it to be serviced without accessing the
* The table was vacuumed and analyzed after the index was created (I even
set the col statistics to 1000)
Now, for the queries:
QUERY 1: select sobjid from p1_nrn_road where v = 2226497481090365991
The plan is "Index scan using p1_nrn_road_v on p1_nrn_road (cost=0.00..6.52
The plan was close: only one row is returned. Everything is fine here.
QUERY 2: select sobjid from p1_nrn_road where v = 1
The plan is "Seq Scan on p1_nrn_road (cost=0.00..22158.54 rows=2 width=8)"
The plan is wrong: "select min(v) from p1_nrn_road" returns
2226497481090365991, which indicates that the query will return 0 rows.
I can't understand why a sequential scan is selected for query 2 when the
plan suggests only two rows (high selectivity) are expected.
Oracle doesn't behave like this, and I expect Postgresql to behave similarly
(are my expectations too high?)
Thank you in advance for any assistance you can provide.
pgsql-general by date
|Next:||From: Michal Hlavac||Date: 2004-07-22 08:02:19|
|Subject: Tsearch2 - list of words or phrase with count|
|Previous:||From: Andrew Bartley||Date: 2004-07-22 03:23:25|
|Subject: Hex to char|