pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedplanner doesn't use multicolumn index
4+ messages / 4 participants
[nested] [flat]
* planner doesn't use multicolumn index
@ 2003-10-08 14:08 Adrian Demaestri <ademaestri@yahoo.com>
0 siblings, 3 replies; 4+ messages in thread
From: Adrian Demaestri @ 2003-10-08 14:08 UTC (permalink / raw)
To: pgsql-performance
We've a table with about 8 million rows, and we need to get rows by the value of two of its fields( the type of the fields are int2 and int4, the where condition is v.g. partido=99 and partida=123). We created a multicolumn index on that fields but the planner doesn't use it, it still use a seqscan. That fields are primary key of the table and we clusterded the table based on that index, but it still doesn't work. We also set the enviroment variable enable_seqscan to false and nathing happends. The only way the planner use it is in querys that order by the expression of the index.
Any idea?
thanks.
Adrián
---------------------------------
Do You Yahoo!?
Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
Visíta Yahoo! Noticias.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: planner doesn't use multicolumn index
@ 2003-10-08 14:18 Tomasz Myrta <jasiek@klaster.net>
parent: Adrian Demaestri <ademaestri@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-10-08 14:18 UTC (permalink / raw)
To: Adrian Demaestri <ademaestri@yahoo.com>; +Cc: pgsql-performance
> We've a table with about 8 million rows, and we need to get rows by the
> value of two of its fields( the type of the fields are int2 and int4,
> the where condition is v.g. partido=99 and partida=123). We created a
> multicolumn index on that fields but the planner doesn't use it, it
> still use a seqscan. That fields are primary key of the table and we
> clusterded the table based on that index, but it still doesn't work. We
> also set the enviroment variable enable_seqscan to false and nathing
> happends. The only way the planner use it is in querys that order by the
> expression of the index.
> Any idea?
> thanks.
> Adrián
where partido=99::int2 and partida=123;
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: planner doesn't use multicolumn index
@ 2003-10-08 14:27 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Adrian Demaestri <ademaestri@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Shridhar Daithankar @ 2003-10-08 14:27 UTC (permalink / raw)
To: Adrian Demaestri <ademaestri@yahoo.com>; +Cc: pgsql-performance
Adrian Demaestri wrote:
> We've a table with about 8 million rows, and we need to get rows by the value
>of two of its fields( the type of the fields are int2 and int4,
>the where condition is v.g. partido=99 and partida=123). We created a
>multicolumn index on that fields but the planner doesn't use it, it still use
>a seqscan. That fields are primary key of the table and we clusterded the table
>based on that index, but it still doesn't work. We also set the enviroment
> variable enable_seqscan to false and nathing happends. The only way the
>planner use it is in querys that order by the expression of the index.
Use partido=99::int2 and partida=123::int4
Match the data types basically..
Shridhar
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: planner doesn't use multicolumn index
@ 2003-10-08 14:35 Manfred Koizar <mkoi-pg@aon.at>
parent: Adrian Demaestri <ademaestri@yahoo.com>
2 siblings, 0 replies; 4+ messages in thread
From: Manfred Koizar @ 2003-10-08 14:35 UTC (permalink / raw)
To: Adrian Demaestri <ademaestri@yahoo.com>; +Cc: pgsql-performance
On Wed, 8 Oct 2003 09:08:59 -0500 (CDT), Adrian Demaestri
<ademaestri@yahoo.com> wrote:
>the type of the fields are int2 and
>int4, the where condition is v.g. partido=99 and partida=123).
Write your search condition as
WHERE partido=99::int2 and partida=123
Servus
Manfred
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-10-08 14:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-08 14:08 planner doesn't use multicolumn index Adrian Demaestri <ademaestri@yahoo.com>
2003-10-08 14:18 ` Tomasz Myrta <jasiek@klaster.net>
2003-10-08 14:27 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-10-08 14:35 ` Manfred Koizar <mkoi-pg@aon.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox