1

i have an issue where i have no idea anymore and can't seem to find a similar one already discussed, so...

Am/have to be on pgsql 10.15 / RHEL x86_64.

  1. rel A has about 813 million records.
  2. format is like id,....,snapzeit.
  3. col snapzeit is not nullable, nonunique, timestamp.
  4. there is an index on date_trunc('minute', snapzeit).
  5. no refint constraints involved.
  6. recently analyzed

rel B is only one col named t of timestamp and has like 13 rows; unique timestamps. rel B is only there because i came from working with generate_series, which was even slower than my current issue.

I want to have all from A where snapzeit = B.t, which would be around 255k rows.

If i filter by giving a list of literals (same values as in B) it is lightning fast:

select * from A
where date_trunc('minute',snapzeit) in ('2021-11-01 04:30',...);

query plan:

Bitmap Heap Scan on A  (cost=1294171.21..24450480.97 rows=69102630 width=193) (actual time=16.184..278.121 rows=255589 loops=1)
  Recheck Cond: (date_trunc('minute'::text, snapzeit) = ANY ('{"2021-11-17 04:00:00",$CUT_OUT$'::timestamp without time zone[]))
  Heap Blocks: exact=5879
  ->  Bitmap Index Scan on IDX_A  (cost=0.00..1276895.55 rows=69102630 width=0) (actual time=15.367..15.368 rows=255589 loops=1)
        Index Cond: (date_trunc('minute'::text, snapzeit) = ANY ('{"2021-11-17 04:00:00",$CUT_OUT$'::timestamp without time zone[]))
Planning time: 0.157 ms
Execution time: 456.024 ms

If i use rel B it takes ages. I added a second predicate so it comes back in 2021 at all:

select * from A
where date_trunc('minute',snapzeit) in (
    select date_trunc('minute',t) from B
)
and snapzeit >= '2021-11-10';

query plan:

Nested Loop  (cost=622164.37..18870692.83 rows=12866340 width=193) (actual time=3051.373..42762.069 rows=255589 loops=1)
  ->  HashAggregate  (cost=1.21..1.38 rows=17 width=8) (actual time=0.057..0.133 rows=17 loops=1)
        Group Key: date_trunc('minute'::text, B.t)
        ->  Seq Scan on test  (cost=0.00..1.17 rows=17 width=8) (actual time=0.024..0.039 rows=17 loops=1)
  ->  Bitmap Heap Scan on A  (cost=622163.16..1108754.04 rows=128663 width=193) (actual time=2478.495..2492.003 rows=15035 loops=17)
        Recheck Cond: ((date_trunc('minute'::text, snapzeit) = date_trunc('minute'::text, B.t)) AND (snapzeit >= '2021-11-10 00:00:00'::timestamp without time zone))
        Heap Blocks: exact=5879
        ->  BitmapAnd  (cost=622163.16..622163.16 rows=128663 width=0) (actual time=2478.424..2478.424 rows=0 loops=17)
              ->  Bitmap Index Scan on IDX_A  (cost=0.00..75117.91 rows=4065181 width=0) (actual time=0.870..0.870 rows=15035 loops=17)
                    Index Cond: (date_trunc('minute'::text, snapzeit) = date_trunc('minute'::text, B.t))
              ->  Bitmap Index Scan on IDX2_A (cost=0.00..540579.67 rows=25732679 width=0) (actual time=2464.373..2464.373 rows=36137255 loops=17)
                    Index Cond: (snapzeit >= '2021-11-10 00:00:00'::timestamp without time zone)
Planning time: 0.313 ms
Execution time: 42936.373 ms

rel A has also an IDX on snapzeit without that date_trunc.

Tried variations with ANY(), inner join A and B on A.snapzeit=B.t, without date_trunc in subselect; it stays slow.

My expectation would be a similar performance as in the "literal" case; so, where comes all the action from in the second case while it could go with those handful of values to the index and that's it ?

On the other hand i feel i overlook something easy/obvious. Any hint ?

Cheers and thanx alot, JJ

Edit:

Enter array() and all goes fast:

select * from A
    where date_trunc('minute',snapzeit) = ANY(ARRAY (
        select date_trunc('minute',t) from B
    ));

Solved by @LaurenzAlbe, Thanx.

While i'd still say the planner/optimizer should do that on its own.

Cheers, JJ

6
  • Is the behavior the same if you replace IN (SELECT ...) with = ANY (array(SELECT ...))? Nov 18, 2021 at 12:36
  • @LaurenzAlbe No, it is not. That seems to be the solution ! It is superfast for case 2 when i give array() and use =any(). any() without array() still slow. What is it ? Nov 18, 2021 at 12:51
  • It is guesswork. PostgreSQL can use an index scan with = ANY (as you see in your "fast" execution plan). Nov 18, 2021 at 12:56
  • @LaurenzAlbe True..but wouldn't that transform on an argument be the planner's job ? Just to have my expectaions right. Nov 18, 2021 at 12:58
  • Yes, but obviously that doesn't happen, so you have to help the planner. Nov 18, 2021 at 13:00

1 Answer 1

1

Experimentation shows that the following is enough to make PostgreSQL use the faster index scan: change

WHERE date_trunc('minute',snapzeit) IN (SELECT ...)

to

WHERE date_trunc('minute',snapzeit) = ANY (array(SELECT ...))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.