On Fri, Sep 22, 2017 at 10:45 AM, Rafia Sabih <rafia.sa...@enterprisedb.com> wrote: >> >> On completing the benchmark for all queries for the above mentioned >> setup, following performance improvement can be seen, >> Query | Patch | Head >> 3 | 1455 | 1631 >> 4 | 499 | 4344 >> 5 | 1464 | 1606 >> 10 | 1475 | 1599 >> 12 | 1465 | 1790 >> >> Note that all values of execution time are in seconds. > > I compared this experiment with non-partitioned database and following > is the result, > Query | Non-partitioned head > 3 | 1752 > 4 | 315 > 5 | 2319 > 10 | 1535 > 12 | 1739 > > In summary, the query that appears slowest in partitioned database is > not so otherwise. It is good to see that in Q4 partition-wise join > helps in achieving performance closer to it's non-partitioned case, > otherwise partitioning alone causes it to suffer greatly. Apart from > Q4 it does not looks like partitioning hurts anywhere else, though the > maximum improvement is ~35% for Q5. > Another point to note here is that the performance on partitioned and > unpartitioned heads are quite close (except Q4) which is something > atleast I wasn't expecting. It looks like we need not to partition the > tables anyway, or atleast this set of queries doesn't benefit from > partitioning. Please let me know if somebody has better ideas on how > partitioning schemes should be applied to make it more beneficial for > these queries.
Just partitioning is not expected to improve query performance (but we still see some performance improvement). Partitioning + partition-wise operations, pruning is expected to show performance gains. IIUC the results you reported, Q3 takes 1752 seconds with non-partitioned head, with partitioning it completes in 1631 seconds and with partition-wise join it completes in 1455, so net improvement because of partitioning is 300 seconds is almost 16% improvement, which is a lot for very large data. So, except Q4, every query improves when the tables are partitioned. Am I interpreting the results correctly? There may be some other way of partitioning, which may give better results, but I think what we have now shows the importance of partitioning in case of very large data e.g. scale 300 TPCH. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
