On Tue, Jul 25, 2017 at 8:59 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Jul 25, 2017 at 1:31 AM, Rafia Sabih > <rafia.sa...@enterprisedb.com> wrote: >> - other queries show a good 20-30% improvement in performance. Performance >> numbers are as follows, >> >> Query| un_part_head (seconds) | part_head (seconds) | part_patch (seconds) | >> 3 | 76 |127 | 88 | >> 4 |17 | 244 | 41 | >> 5 | 52 | 123 | 84 | >> 7 | 73 | 134 | 103 | >> 10 | 67 | 111 | 89 | >> 12 | 53 | 114 | 99 | >> 18 | 447 | 709 | 551 | > > Hmm. This certainly shows that benefit of the patch, although it's > rather sad that we're still slower than if we hadn't partitioned the > data in the first place. Why does partitioning hurt performance so > much?
I was analysing some of the plans (without partition and with partition), Seems like one of the reasons of performing worse with the partitioned table is that we can not use an index on the partitioned table. Q4 is taking 17s without partition whereas it's taking 244s with partition. Now if we analyze the plan Without partition, it can use parameterize index scan on lineitem table which is really big in size. But with partition, it has to scan this table completely. -> Nested Loop Semi Join -> Parallel Bitmap Heap Scan on orders -> Bitmap Index Scan on idx_orders_orderdate (cost=0.00..24378.88 r -> Index Scan using idx_lineitem_orderkey on lineitem (cost=0.57..29.29 rows=105 width=8) (actual time=0.031..0.031 rows=1 loops=1122364) Index Cond: (l_orderkey = orders.o_orderkey) Filter: (l_commitdate < l_receiptdate) Rows Removed by Filter: 1 -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers