Re: Partition tables to improve select speed?

On Jan 31, 2013, at 4:43 AM, Bert wrote:

> Hello,
>
> We are using postgres as a datawarehouse.
> We typically use very complex queries, and they seem to work very well.
>
> We have several BIG tables. we chose to partition those tables on a specific key.
>
> We also adapted our selects, and added a 'where' clause which helps postgres's query planner, so only one of the partition needs to be used in stead of 32.
>
> How ever, we get some strange results. For some queries the runtime really is a lot faster. But for the runtime is a even a bit longer.
>
> Can anyone give me insight on why that can happen?
>
> wkr,
>
> Bert

Hi Bert,

Check out section 5.9.4 on this page: http://www.postgresql.org/docs/current/static/ddl-partitioning.html - do you have your constraint exclusion setup appropriately? If so, are your conditions extending outside of the partitions you are looking in.

Using EXPLAIN to see what query plans are being generated should shed some light into this, to see if you are indeed doing table lookups in partitions inadvertently based on your exclusion constraints.

Best,

Jonathan