pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
planner 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