Re: BUG #16163: Seq scan through all the partitions on a partitioned table when joined small, dictionary table.

Hello,

On Fri, Dec 13, 2019 at 12:21 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 16163
> Logged by: Sebastian Ćmak
> Email address: seb55(at)op(dot)pl
> PostgreSQL version: 12.1
> Operating system: Linux
> Description:
>
> 1. Create partitioned table:
> CREATE TABLE dw.fact_registers_decret_date
> (
> id bigserial,
> dim_date_decret_date_id integer NOT NULL,
> CONSTRAINT fact_registers_decret_date_pk PRIMARY KEY
> (dim_date_decret_date_id, id),
> ) PARTITION BY LIST (dim_date_decret_date_id) ;
>
> CREATE INDEX fact_registers_decret_date_i7
> ON dw.fact_registers_decret_date USING btree
> (dim_date_decret_date_id)
> ;
>
>
> CREATE INDEX fact_registers_decret_date_i7
> ON dw.fact_registers_decret_date USING btree
> (dim_date_decret_date_id)
> ;
>
> -- Partitions SQL
...

> CREATE TABLE temp.dim_dates
> (
> id integer,
> date_val date
> )
> ;
>
> 2. Fill dw.fact_registers_decret_date with approx. 20 mln records per
> partition. Analyse the table.
> 3. Put 1 record to the table temp.dim_dates. Analyse the table.
> Select statement:
> select frdd.* from dw.fact_registers_decret_date frdd
> join temp.dim_dates dd on frdd.dim_date_decret_date_id=dd.id;
>
> It does not use neither existing index on dw.fact_registers_decret_date nor
> partition limitation. The query lasts hours.
>
> Execution plan:
> -----------------------------------------
> QUERY PLAN DESCRIPTION
> -----------------------------------------
>
> EXPLAIN (ANALYZE off, VERBOSE on, COSTS on, BUFFERS off, TIMING off) select
> frdd.* from dw.fact_registers_decret_date frdd
> join temp.dim_dates dd on frdd.dim_date_decret_date_id=dd.id
>
> Gather (cost=1001.02..42013786.93 rows=11865543 width=120)
> Output: frdd.id, frdd.audit_cd, frdd.audit_cu, frdd.audit_ca,
> frdd.contract_id, frdd.ct, frdd.dt, frdd.balance,
> frdd.dim_date_decret_date_id, frdd.dim_register_id, frdd.dim_wallet_id,
> frdd.dim_owner_type_id, frdd.dim_is_debt_id, frdd.dim_is_obsolete_id,
> frdd.dim_is_current_id, frdd.nrb
> Workers Planned: 8
> -> Hash Join (cost=1.02..40826232.63 rows=1483193 width=120)

Hmm, I don't think there is any bug here.

It's reasonable to expect partition pruning to eliminate unnecessary
partition scans, but the above query needs to be rewritten for
partition pruning to occur. In fact, didn't you mean to join using
frdd.dim_date_decret_date_id= to_char(dd.date_val,
'YYYYMMDD')::integer.

Also, partition pruning can only occur if nested loop join is used,
not with hash or merge join. So, you will need to disable hash and
merge join too. Maybe we'll need to make the planner smarter about
this in the future though, that is, choose nested loop over hash/merge
if the former can use partition pruning.

Thanks,
Amit