Partitions and joins lead to index lookups on all partitions

Hi there,

 

Currently, we are running into serious performance problems with our paritioning setup, because index lookups are mostly done on allpartions, in stead of the one partition it should know that it can find the needed row.

 

Simple example, were we have a partitioned tables named part_table. So here it goes:

 

select * from part_table where id = 12123231

 

Will do an index lookup only in the partition that it knows it can find the id there. However:

 

select * from part_table where id = (select 12123231)

 

Will do an index lookup in ALL partitions, meaning it is significantly slower, even more since the database will not fit into memory.

 

So okay, we could just not use parameterized queries... Well.. not so fast. Consider a second table referencing to the first:

 

ref_table:

group_id bigint

part_table_id bigint

 

Now when I join the two:

select part_table.* from part_table

join ref_table on (ref_table.part_table_id = part_table.id and group_id = 12321)

 

It will also do index loopups on ALL partitions. 

 

How do we handle this? Above queries are simplified versions of the things gooing on but the idea is clear. I tried dooing this in 9.1 (we are currently using 9.0), but this does not matter. So what is actually the practicial use of partitioning if you can't even use it effectively for simple joins?

 

constraint_exclusion is enabled correctly, and as far as I can see, this behaviour is according to the book.

 

Are there any progresses in maybe 9.2 to make this any better? If not, how schould we handle this? We can also not choose to parition, but how will that perform on a 100 GB table?

 

Kind regards,

 

Christiaan Willemsen