Same query, different times

Question

What makes performance vary for the same query? My DB has just ~10 tables and no more than a few thousand rows.

Here's the query:

select
  c.first_name,
  c.last_name,
  c.user_id,
  c.photo_url,
  s.dialogues
  from contributors c
    join (
      select count(*) dialogues, user_id from (
          select contributor_one_uuid user_id from dialogues
          union all 
          select contributor_two_uuid from dialogues
      ) stats group by user_id
    ) s on (s.user_id = c.user_id)
  where c.visible = true

#1: It takes almost 17 seconds to execute! explain (analyze, buffers) select on the query:

Seq Scan on contributors c  (cost=0.00..4205.86 rows=259 width=109) (actual time=0.073..16819.258 rows=260 loops=1)
  Filter: visible
  Rows Removed by Filter: 13
  Buffers: shared hit=3681
  SubPlan 1
    ->  Aggregate  (cost=16.06..16.07 rows=1 width=8) (actual time=64.548..64.549 rows=1 loops=260)
          Buffers: shared hit=3640
          ->  Seq Scan on dialogues  (cost=0.00..16.05 rows=2 width=0) (actual time=49.155..64.547 rows=1 loops=260)
                Filter: ((c.user_id = contributor_one_uuid) OR (c.user_id = contributor_two_uuid))
                Rows Removed by Filter: 136
                Buffers: shared hit=3640
Planning Time: 0.136 ms
Execution Time: 16819.365 ms

#2. It takes not even a second to execute!

Seq Scan on contributors c  (cost=0.00..4205.86 rows=259 width=109) (actual time=0.063..801.278 rows=260 loops=1)
  Filter: visible
  Rows Removed by Filter: 13
  Buffers: shared hit=3681
  SubPlan 1
    ->  Aggregate  (cost=16.06..16.07 rows=1 width=8) (actual time=3.080..3.080 rows=1 loops=260)
          Buffers: shared hit=3640
          ->  Seq Scan on dialogues  (cost=0.00..16.05 rows=2 width=0) (actual time=0.009..3.079 rows=1 loops=260)
                Filter: ((c.user_id = contributor_one_uuid) OR (c.user_id = contributor_two_uuid))
                Rows Removed by Filter: 136
                Buffers: shared hit=3640
Planning Time: 0.127 ms
Execution Time: 801.379 ms

user_6436191 · Answer

The engine is performing a SeqScan on contributors and I think that's unavoidable (unless I'm quite mistaken). However, it's also performing a SeqScan on dialogues and this can be prevented with a lateral join.

If the table dialogues has indexes on contributor_one_uuid and also on contributor_two_uuid the query can be rephrased. Hopefully this change can speed it up:

select
  c.first_name,
  c.last_name,
  c.user_id,
  c.photo_url,
  s.dialogues
from contributors c,
lateral (
  select (select count(*) from dialogues d where d.contributor_one_uuid = c.user_id)
       + (select count(*) from dialogues d where d.contributor_two_uuid = c.user_id) 
    as dialogues
) s on true

Same query, different times

1 Answers