Postgres Pro
Downloads
Home   >   mailing lists

Thread: Forcing the use of one index instead other.

Forcing the use of one index instead other.

From
"Edmundo Robles L."
Date:
Hello there!


I have two index  with a same field into them like this:

create index  numberfail on Events (numberfail);
and
create index failtype on  Events (numberfail,eventtype);



then i ran explain analyze  and always  took the numberfail index but i wish  the  failtype index;



this is the query:


SELECT * FROM events WHERE numberfail=194 AND eventtype='XXX';

What should i to do to force the use of failtypeindex????

I though maybe  if  i delete the index numberfail could works, but i don't know  if the  another program
will fail  if the numberfail index does'nt exists.


well, thats all for the moment and i appreciate your time in reading this mail!!!
See you.
--
SENSA Control Digital.
Ing. Edmundo Robles Lopez.
Analista Programador.

Re: Forcing the use of one index instead other.

From
Ries van Twisk
Date:
You could remove the index numberfail.

PG can use the index failtype in the case you just need a where clause
on numberfail also....

Ries

>
> Hello there!
>
>
> I have two index  with a same field into them like this:
>
> create index  numberfail on Events (numberfail);
> and
> create index failtype on  Events (numberfail,eventtype);
>
>
>
> then i ran explain analyze  and always  took the numberfail index
> but i wish  the  failtype index;
>
>
>
> this is the query:
>
>
> SELECT * FROM events WHERE numberfail=194 AND eventtype='XXX';
>
> What should i to do to force the use of failtypeindex????
>
> I though maybe  if  i delete the index numberfail could works, but i
> don't know  if the  another program
> will fail  if the numberfail index does'nt exists.
>
>
> well, thats all for the moment and i appreciate your time in reading
> this mail!!!
> See you.
> --
> SENSA Control Digital.
> Ing. Edmundo Robles Lopez.
> Analista Programador.
>
>



            regards, Ries van Twisk


-------------------------------------------------------------------------------------------------
Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: ries@vantwisk.nl
web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196
SIP: +1-747-690-5133

Re: Forcing the use of one index instead other.

From
Tom Lane
Date:
"Edmundo Robles L." <erobles@sensacd.com.mx> writes:
> I have two index  with a same field into them like this:

> create index  numberfail on Events (numberfail);
> and
> create index failtype on  Events (numberfail,eventtype);

> then i ran explain analyze  and always  took the numberfail index but
i wish  the  failtype index;

Uh, why?  If you have the smaller index available it's hard to imagine
any sane reason not to use it.

> I though maybe  if  i delete the index numberfail could works, but i don't know  if the  another program
> will fail  if the numberfail index does'nt exists.

No, you can drop that one if you want to.  You might care to read the
fine manual about index design:
http://www.postgresql.org/docs/8.3/static/indexes.html

            regards, tom lane