Skip site navigation (1) Skip section navigation (2)

Partitions and joins lead to index lookups on all partitions

From: Christiaan Willemsen <cwillemsen(at)technocon(dot)com>
To: pgsql-performance(at)postgresql(dot)org <pgsql-performance(at)postgresql(dot)org>
Subject: Partitions and joins lead to index lookups on all partitions
Date: 2011-12-07 15:15:52
Message-ID: zarafa.4edf8328.2dfa.0b8fae0b2af83bae@meel.technocon.local (view raw or flat)
Thread:
Lists: pgsql-performance
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

 

 
 

 
 

 
 

 
 

 

Responses

pgsql-performance by date

Next:From: Anibal David AcostaDate: 2011-12-07 15:34:30
Subject: autovacuum, any log?
Previous:From: Kevin GrittnerDate: 2011-12-07 15:14:11
Subject: Re: Question about VACUUM

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group