Index not being used ?

[Carlos G Mendioroz]

Hi,
I'm trying to understand why a perfect match index is not being used,
and a sequence scan is done in place:

PostgreSQL 7.3.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2
20020927 (prerelease)

I've a table with 7M records, and an index on 3 fields:

CREATE TABLE public.base (
nombre varchar(255),
calle varchar(255),
puerta int2,
resto varchar(255),
lid int2,
area varchar(4),
telefono varchar(10)
)
CREATE INDEX base_dir ON base USING btree (lid, calle, puerta);

And trying the following select:

select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10

yields

Seq Scan on base (cost=100000000.00..100212801.12 rows=1 width=63)
Filter: ((lid = 457) AND (calle = 'MALABIA'::character varying) AND
(puerta = 10))

even with enable_seqscan set to off, as you may have guessed.

What am I missing here ?
(There's another index on area and telefono which works as expected,
so it's not a LOCALE problem AFAIK).

--
Carlos G Mendioroz <tr**@huapi.ba.ar> LW7 EQI Argentina
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Adam Kavan]

CREATE TABLE public.base (
nombre varchar(255),
calle varchar(255),
puerta int2,
resto varchar(255),
lid int2,
area varchar(4),
telefono varchar(10)
)
CREATE INDEX base_dir ON base USING btree (lid, calle, puerta);

And trying the following select:

select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10

I think its because lid and puerta are int2's and 457 and 10 are
int4's. Try lid = '457'::int2 and puerta = '10'::int2. To use an index
the variables have to match types exactly.

--- Adam Kavan
--- ak****@cox.net
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ron Johnson]

On Wed, 2003-09-03 at 16:34, Adam Kavan wrote:

CREATE TABLE public.base (
nombre varchar(255),
calle varchar(255),
puerta int2,
resto varchar(255),
lid int2,
area varchar(4),
telefono varchar(10)
)
CREATE INDEX base_dir ON base USING btree (lid, calle, puerta);

And trying the following select:

select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10

I think its because lid and puerta are int2's and 457 and 10 are
int4's. Try lid = '457'::int2 and puerta = '10'::int2. To use an index
the variables have to match types exactly.

Quotes and casting together are not necessary.
Either of these will work:
lid = '457' and puerta = '10'
lid = 457::int2 and puerta = 10::int2

I'd choose "lid = 457::int2" since it's telling PG what the datatype
is, whereas with lid = '457', PG must figure it out.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

After seeing all the viruses, trojan horses, worms and Reply
mails from stupidly-configured anti-virus software that's been
hurled upon the internet for the last 3 years, and the
time/money that is spent proteting against said viruses, trojan
horses & worms, I can only conclude that Microsoft is dangerous
to the internet and American commerce, and it's software should
be banned.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

On Tue, 2 Sep 2003, Carlos G Mendioroz wrote:

Hi,
I'm trying to understand why a perfect match index is not being used,
and a sequence scan is done in place:

PostgreSQL 7.3.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2
20020927 (prerelease)

I've a table with 7M records, and an index on 3 fields:

CREATE TABLE public.base (
nombre varchar(255),
calle varchar(255),
puerta int2,
resto varchar(255),
lid int2,
area varchar(4),
telefono varchar(10)
)
CREATE INDEX base_dir ON base USING btree (lid, calle, puerta);

And trying the following select:

select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10

Try one of
lid='457' and puerta='10'
lid=457::int2 and puerta=10::int2
lid=CAST(457 as int2) and puerta=CAST(10 as int2)

The constants get typed as int4 and so it doesn't realize it can use the
index, you can check the archives for more information.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Ron Johnson <ro***********@cox.net> writes:

Quotes and casting together are not necessary.
Check.
I'd choose "lid = 457::int2" since it's telling PG what the datatype
is, whereas with lid = '457', PG must figure it out.

I'd lean to the quotes, actually, since that way your application's
code isn't so dependent on the particular datatypes in the table.
I doubt there's any performance advantage to writing the cast, if
that's what you were thinking ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ron Johnson]

On Wed, 2003-09-03 at 22:27, Tom Lane wrote:

Ron Johnson <ro***********@cox.net> writes:

Quotes and casting together are not necessary.

Check.

I'd choose "lid = 457::int2" since it's telling PG what the datatype
is, whereas with lid = '457', PG must figure it out.

I'd lean to the quotes, actually, since that way your application's
code isn't so dependent on the particular datatypes in the table.
I doubt there's any performance advantage to writing the cast, if
that's what you were thinking ...

Well, yes, that's what I was thinking: minimize the code path that
the parser must take.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Basically, I got on the plane with a bomb. Basically, I tried
to ignite it. Basically, yeah, I intended to damage the plane."
RICHARD REID, tried to blow up American Airlines Flight 63
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Carlos G Mendioroz]

Great! Many thanks.
Casting the type did the trick.

Even though it is somehow clear now, I don't see why the type is (can)
not (be) inferred from the field type.
--
Carlos G Mendioroz <tr**@huapi.ba.ar> LW7 EQI Argentina
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org