Table access method not behaving like Oracle (index vs sequential scan). Examples and stats provided.

From: "Kevin Macdonald" <nospam(at)please(dot)thankyou>
To: pgsql-general(at)postgresql(dot)org
Subject: Table access method not behaving like Oracle (index vs sequential scan). Examples and stats provided.
Date: 2004-07-22 04:34:26
Message-ID: mtHLc.998$gEL.224@news04.bloor.is.net.cable.rogers.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I expected Postgresql to use an indexed access method, but in certain cases
it is using a sequential scan. Details are below:

Table:

P1_NRN_ROAD (
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
underlying table)
* 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
rows=2 width=8)"

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michal Hlavac 2004-07-22 08:02:19 Tsearch2 - list of words or phrase with count
Previous Message Andrew Bartley 2004-07-22 03:23:25 Hex to char