Re: [HACKERS] path toward faster partition pruning - Mailing list pgsql-hackers

Hi Amit,

On 12/21/2017 11:25 PM, Amit Langote wrote:
> Thanks again.
> 
> Please find attached updated patches.
> 

I have been looking at this patch from a simple hash partition point of 
view.

-- ddl.sql --
CREATE TABLE t1 (
     a integer NOT NULL,
     b integer NOT NULL
) PARTITION BY HASH (b);

CREATE TABLE t1_p00 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 0);
CREATE TABLE t1_p01 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 1);
CREATE TABLE t1_p02 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 2);
CREATE TABLE t1_p03 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 3);

CREATE INDEX idx_t1_b_a_p00 ON t1_p00 USING btree (b, a);
CREATE INDEX idx_t1_b_a_p01 ON t1_p01 USING btree (b, a);
CREATE INDEX idx_t1_b_a_p02 ON t1_p02 USING btree (b, a);
CREATE INDEX idx_t1_b_a_p03 ON t1_p03 USING btree (b, a);

INSERT INTO t1 (SELECT i, i FROM generate_series(1, 1000000) AS i);

ANALYZE;
-- ddl.sql --

w/

-- select.sql --
\set b random(1, 1000000)
BEGIN;
SELECT t1.a, t1.b FROM t1 WHERE t1.b = :b;
COMMIT;
-- select.sql --

using pgbench -c X -j X -M prepared -T X -f select.sql part-hash

On master we have generic_cost planning cost of 33.75, and an 
avg_custom_cost of 51.25 resulting in use of the generic plan and a TPS 
of 8893.

Using v17 we have generic_cost planning cost of 33.75, and an 
avg_custom_cost of 25.9375 resulting in use of the custom plan and a TPS 
of 7129 - of course due to the generation of a custom plan for each 
invocation.

Comparing master with an non-partitioned scenario; we have a TPS of 
12968, since there is no overhead of ExecInitAppend (PortalStart) and 
ExecAppend (PortalRun).

Could you share your thoughts on

1) if the generic plan mechanics should know about the pruning and hence 
give a lower planner cost

1) if the patch should be more aggressive in removing planning nodes 
that aren't necessary, e.g. going from Append -> IndexOnly to just 
IndexOnly.

I have tested with both [1] and [2], but would like to know about your 
thoughts on the above first.

Thanks in advance !

[1] https://commitfest.postgresql.org/16/1330/
[2] https://commitfest.postgresql.org/16/1353/

Best regards,
  Jesper