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

[Kevin Macdonald]

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 = 222649748109036 5991

The plan is "Index scan using p1_nrn_road_v on p1_nrn_road (cost=0.00..6.5 2
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..221 58.54 rows=2 width=8)"

The plan is wrong: "select min(v) from p1_nrn_road" returns
222649748109036 5991, 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.

[Daniel Schuchardt]

Kevin Macdonald wrote:

QUERY 2: select sobjid from p1_nrn_road where v = 1

The plan is "Seq Scan on p1_nrn_road (cost=0.00..221 58.54 rows=2 width=8)"

The plan is wrong: "select min(v) from p1_nrn_road" returns
222649748109036 5991, which indicates that the query will return 0 rows.

Hy Kevin,

please try

select sobjid from p1_nrn_road where v = '1'

(notice quotes)

I'm not sure but i think indexes that consists of more than 2 rows have
to be casted to char in PG.

Daniel.

[Greg Stark]

"Kevin Macdonald" <no****@please. thankyou> writes:

QUERY 2: select sobjid from p1_nrn_road where v = 1

The plan is "Seq Scan on p1_nrn_road (cost=0.00..221 58.54 rows=2 width=8)"

Incidentally, you should send the whole plan. In general you should send all
the information you have, not just the parts you think are relevant. The next
line would have clearly shown the problem to someone who knew what to look
for.

try
where v = '1'
or
where v = 1::bigint

The problem is that in 7.4 and previous indexes can't be used for cross-type
comparisons and integer constants are assumed to be integer not bigint type.
If you leave it in quotes then postgres doesn't pick a type until it looks at
what you're comparing it with. Or if you cast it then you force it to be a
bigint=bigint comparison.

7.5 will avoid this problem.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Manfred Koizar]

On Thu, 22 Jul 2004 04:34:26 GMT, "Kevin Macdonald"
<no****@please. thankyou> wrote:

(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)

No, Postgres always consults the heap to get the desired values. The
most obvious reason is that visibility information is stored in heap
tuples but not in index tuples.

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org