Postgres Pro
Downloads
Home   >   mailing lists

Thread: Index not being used ?

Index not being used ?

From
Carlos G Mendioroz
Date:
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

Re: Index not being used ?

From
Adam Kavan
Date:
>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

Re: Index not being used ?

From
Ron Johnson
Date:
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.

Re: Index not being used ?

From
Stephan Szabo
Date:
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.

Re: Index not being used ?

From
Tom Lane
Date:
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

Re: Index not being used ?

From
Ron Johnson
Date:
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

Re: Index not being used ?

From
Carlos G Mendioroz
Date:
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