Обсуждение: Partition tables to improve select speed?
Hello,
We are using postgres as a datawarehouse. 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.
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,
wkr,
Bert
On Jan 31, 2013, at 4:43 AM, Bert wrote:
We have several BIG tables. we chose to partition those tables on a specific key.We typically use very complex queries, and they seem to work very well.Hello,We are using postgres as a datawarehouse.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