Skip site navigation (1) Skip section navigation (2)

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

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Table access method not behaving like Oracle (index vs sequential scan). Examples and stats provided.
Date: 2004-07-29 15:56:46
Message-ID: 87isc6rem9.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-general
"Kevin Macdonald" <nospam(at)please(dot)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..22158.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


In response to

pgsql-general by date

Next:From: Doug McNaughtDate: 2004-07-29 16:07:42
Subject: Re: Question about linux filesystem and psql
Previous:From: Jim SeymourDate: 2004-07-29 15:56:21
Subject: Re: mirroring data on different drives?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group