Обсуждение: Index not being used ?

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  <tron@huapi.ba.ar>  LW7 EQI  Argentina

>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
--- akavan@cox.net

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. ron.l.johnson@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.

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.

Ron Johnson <ron.l.johnson@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

On Wed, 2003-09-03 at 22:27, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@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. ron.l.johnson@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

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  <tron@huapi.ba.ar>  LW7 EQI  Argentina