pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mike22e@gmail.com
Subject: BUG #16749: EXPLAIN only shows filtering from the first query in a union chain when using distinct on.
Date: Fri, 27 Nov 2020 11:25:32 +0000
Message-ID: <16749-7a879a6a752b0843@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16749
Logged by:          Michael Richards
Email address:      mike22e@gmail.com
PostgreSQL version: 12.2
Operating system:   macOS Big Sur (11.0.1)
Description:        

### Bug description:

The query plan produced by EXPLAIN ANALYZE does not show all filters being
applied when querying with distinct on over a union'd set of queries. It
only shows a filter as if there was just a single query (the first one in
the union chain). Confirmed in both Postgres 12 and 13.

### Example to reproduce:

Take the following query:

select * from generate_series(0,20) n1
  left join (
    select distinct on (id) * from (
      select n2 as id, n2+2 as a from generate_series(0,10) n2
      union all
      select n3+5 as id, n3+12 as a from generate_series(0,10) n3
    ) s1 order by id, a
  ) s2 on s2.id = n1 where 20 is distinct from s2.a;

Which produces the correct results (row with column a = 20 is removed), as
shown below:

 n1 | id | a  
----+----+----
  0 |  0 |  2
  1 |  1 |  3
  2 |  2 |  4
  3 |  3 |  5
  4 |  4 |  6
  5 |  5 |  7
  6 |  6 |  8
  7 |  7 |  9
  8 |  8 | 10
  9 |  9 | 11
 10 | 10 | 12
 11 | 11 | 18
 12 | 12 | 19
 14 | 14 | 21
 15 | 15 | 22
 16 |    |   
 17 |    |   
 18 |    |   
 19 |    |   
 20 |    |   
(20 rows)

However, the row that contained a = 20 was produced using n3+12 in the
second query in the union, but the EXPLAIN ANALYZE for this query only shows
the n2+2 as a filter; the n3+12 is nowhere to be seen as a filter in the
query plan.

 Hash Left Join  (cost=1.52..1.79 rows=20 width=12) (actual
time=0.098..0.105 rows=20 loops=1)
   Hash Cond: (n1.n1 = n2.n2)
   Filter: (20 IS DISTINCT FROM ((n2.n2 + 2)))
   Rows Removed by Filter: 1
   ->  Function Scan on generate_series n1  (cost=0.00..0.21 rows=21
width=4) (actual time=0.005..0.007 rows=21 loops=1)
   ->  Hash  (cost=1.24..1.24 rows=22 width=8) (actual time=0.068..0.068
rows=16 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Unique  (cost=0.91..1.02 rows=22 width=8) (actual
time=0.058..0.064 rows=16 loops=1)
               ->  Sort  (cost=0.91..0.96 rows=22 width=8) (actual
time=0.058..0.059 rows=22 loops=1)
                     Sort Key: n2.n2, ((n2.n2 + 2))
                     Sort Method: quicksort  Memory: 26kB
                     ->  Append  (cost=0.00..0.42 rows=22 width=8) (actual
time=0.010..0.024 rows=22 loops=1)
                           ->  Function Scan on generate_series n2 
(cost=0.00..0.14 rows=11 width=8) (actual time=0.009..0.011 rows=11
loops=1)
                           ->  Function Scan on generate_series n3 
(cost=0.00..0.17 rows=11 width=8) (actual time=0.009..0.011 rows=11
loops=1)

The relevant part of the query plan is lines 3-4.

Filter: (20 IS DISTINCT FROM ((n2.n2 + 2)))
Rows Removed by Filter: 1

The above filter did not actually remove any rows at all. The filter that
removed that 1 row should be Filter: (20 IS DISTINCT FROM ((n3.n3 + 12))).

This bug also applies when there are any number of union'd queries, not just
two—the query plan will still only show the filter from the first query in
the union chain. The query results seem to be correct, but the displayed
query plan does not account for it.

Cheers,
Michael



view thread (2+ messages)  latest in thread

Message-ID: <16749-7a879a6a752b0843@postgresql.org>
Permalink:  ../16749-7a879a6a752b0843@postgresql.org/
Also on:    postgresql.org/message-id/16749-7a879a6a752b0843@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mike22e@gmail.com
  Subject: Re: BUG #16749: EXPLAIN only shows filtering from the first query in a union chain when using distinct on.
  In-Reply-To: <16749-7a879a6a752b0843@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox