Re: [HACKERS] path toward faster partition pruning

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