Hi Hackers, I would like to propose a patch for pushing down the scan key to heap.
Currently only in case of system table scan keys are pushed down. I have implemented the POC patch to do the same for normal table scan. This patch will extract the expression from qual and prepare the scan keys. Currently in POC version I have only supported "var OP const" type of qual, because these type of quals can be pushed down using existing framework. Purpose of this work is to first implement the basic functionality and analyze the results. If results are good then we can extend it for other type of expressions. However in future when we try to expand the support for complex expressions, then we need to be very careful while selecting pushable expression. It should not happen that we push something very complex, which may cause contention with other write operation (as HeapKeyTest is done under page lock). Performance Test: (test done in local machine, with all default setting). Setup: ---------- create table test(a int, b varchar, c varchar, d int); insert into test values(generate_series(1,10000000), repeat('x', 30), repeat('y', 30), generate_series(1,10000000)); analyze test; Test query: -------------- select count(*) from test where a < $1; Results: (execution time in ms) ------------ Selectivity Head(ms) Patch(ms) gain 0.01 612 307 49% 0.1 623 353 43% 0.2 645 398 38% 0.5 780 535 31% 0.8 852 590 30% 1 913 730 20% Instructions: (Cpu instructions measured with callgrind tool): Quary : select count(*) from test where a < 100000; Head: 10,815,730,925 Patch: 4,780,047,331 Summary: -------------- 1. ~50% reduction in both instructions as well as execution time. 2. Here we can see ~ 20% execution time reduction even at selectivity 1 (when all tuples are selected). And, reasoning for the same can be that HeapKeyTest is much simplified compared to ExecQual. It's possible that in future when we try to support more variety of keys, gain at high selectivity may come down. WIP patch attached.. Thoughts ? -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com
Description: Binary data
-- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers