explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8VcB

Settings
# exclusive inclusive rows x rows loops node
1. 245.786 15,868.649 ↑ 37,400.0 1 1

GroupAggregate (cost=7,312,625.38..7,575,865.20 rows=37,400 width=24) (actual time=15,868.648..15,868.649 rows=1 loops=1)

  • Group Key: u.site_id, site_attrs.num_attrs
  • Buffers: shared hit=907117 read=66190, temp read=1852 written=1853
2.          

CTE active_site_ids

3. 442.287 562.960 ↓ 1.1 4,782 1

Bitmap Heap Scan on "user" (cost=27,352.91..44,534.33 rows=4,508 width=8) (actual time=123.079..562.960 rows=4,782 loops=1)

  • Recheck Cond: ((site_id >= 0) AND (site_id <= 100) AND (((created >= '2020-02-18 00:00:00'::timestamp without time zone) AND (created <= '2020-02-19 00:00:00'::timestamp without time zone)) OR ((updated >= '2020-02-18 00:00:00'::timestamp without time zone) AND (updated <= '2020-02-19 00:00:00'::timestamp without time zone))))
  • Rows Removed by Index Recheck: 1217399
  • Heap Blocks: exact=796 lossy=39966
  • Buffers: shared hit=50004
4. 17.198 120.673 ↓ 0.0 0 1

BitmapAnd (cost=27,352.91..27,352.91 rows=4,514 width=0) (actual time=120.673..120.673 rows=0 loops=1)

  • Buffers: shared hit=9242
5. 50.260 50.260 ↑ 1.0 329,555 1

Bitmap Index Scan on ix_user_site_id (cost=0.00..12,997.40 rows=338,884 width=0) (actual time=50.260..50.260 rows=329,555 loops=1)

  • Index Cond: ((site_id >= 0) AND (site_id <= 100))
  • Buffers: shared hit=3232
6. 0.002 53.215 ↓ 0.0 0 1

BitmapOr (cost=14,354.13..14,354.13 rows=389,075 width=0) (actual time=53.215..53.215 rows=0 loops=1)

  • Buffers: shared hit=6010
7. 44.925 44.925 ↓ 1.1 367,096 1

Bitmap Index Scan on ix_user_created (cost=0.00..11,453.70 rows=339,714 width=0) (actual time=44.925..44.925 rows=367,096 loops=1)

  • Index Cond: ((created >= '2020-02-18 00:00:00'::timestamp without time zone) AND (created <= '2020-02-19 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=2390
8. 8.288 8.288 ↓ 1.3 61,867 1

Bitmap Index Scan on ix_user_updated (cost=0.00..2,898.17 rows=49,361 width=0) (actual time=8.288..8.288 rows=61,867 loops=1)

  • Index Cond: ((updated >= '2020-02-18 00:00:00'::timestamp without time zone) AND (updated <= '2020-02-19 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=3620
9.          

CTE site_attrs

10. 0.008 1.369 ↑ 360.0 1 1

HashAggregate (cost=169.71..173.31 rows=360 width=16) (actual time=1.367..1.369 rows=1 loops=1)

  • Group Key: site_attribute.site_id
  • Buffers: shared hit=27
11. 0.165 1.361 ↑ 80.8 11 1

Hash Join (cost=105.93..165.27 rows=889 width=16) (actual time=1.036..1.361 rows=11 loops=1)

  • Hash Cond: (site_attribute.site_id = active_site_ids.site_id)
  • Buffers: shared hit=27
12. 0.177 0.177 ↑ 1.0 1,778 1

Seq Scan on site_attribute (cost=0.00..44.78 rows=1,778 width=16) (actual time=0.006..0.177 rows=1,778 loops=1)

  • Buffers: shared hit=27
13. 0.002 1.019 ↑ 200.0 1 1

Hash (cost=103.43..103.43 rows=200 width=8) (actual time=1.019..1.019 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.661 1.017 ↑ 200.0 1 1

HashAggregate (cost=101.43..103.43 rows=200 width=8) (actual time=1.017..1.017 rows=1 loops=1)

  • Group Key: active_site_ids.site_id
15. 0.356 0.356 ↓ 1.1 4,782 1

CTE Scan on active_site_ids (cost=0.00..90.16 rows=4,508 width=8) (actual time=0.001..0.356 rows=4,782 loops=1)

16. 278.014 15,622.863 ↑ 79.9 328,962 1

Sort (cost=7,267,917.73..7,333,634.19 rows=26,286,582 width=24) (actual time=15,523.999..15,622.863 rows=328,962 loops=1)

  • Sort Key: u.site_id, site_attrs.num_attrs
  • Sort Method: external sort Disk: 10944kB
  • Buffers: shared hit=907117 read=66190, temp read=1368 written=1368
17. 104.423 15,344.849 ↑ 79.9 328,962 1

Hash Left Join (cost=117.63..2,411,141.10 rows=26,286,582 width=24) (actual time=568.436..15,344.849 rows=328,962 loops=1)

  • Hash Cond: (u.site_id = site_attrs.site_id)
  • Buffers: shared hit=907117 read=66190
18. 5,636.754 15,239.050 ↑ 44.4 328,962 1

Hash Join (cost=105.93..1,454,589.93 rows=14,603,656 width=16) (actual time=567.042..15,239.050 rows=328,962 loops=1)

  • Hash Cond: (u.site_id = sites.site_id)
  • Buffers: shared hit=907090 read=66190
19. 9,035.327 9,035.327 ↑ 1.0 29,171,397 1

Seq Scan on "user" u (cost=0.00..1,215,349.13 rows=29,207,313 width=16) (actual time=0.018..9,035.327 rows=29,171,397 loops=1)

  • Buffers: shared hit=857086 read=66190
20. 0.003 566.969 ↑ 200.0 1 1

Hash (cost=103.43..103.43 rows=200 width=8) (actual time=566.969..566.969 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=50004
21. 1.293 566.966 ↑ 200.0 1 1

HashAggregate (cost=101.43..103.43 rows=200 width=8) (actual time=566.965..566.966 rows=1 loops=1)

  • Group Key: sites.site_id
  • Buffers: shared hit=50004
22. 565.673 565.673 ↓ 1.1 4,782 1

CTE Scan on active_site_ids sites (cost=0.00..90.16 rows=4,508 width=8) (actual time=123.082..565.673 rows=4,782 loops=1)

  • Buffers: shared hit=50004
23. 0.005 1.376 ↑ 360.0 1 1

Hash (cost=7.20..7.20 rows=360 width=16) (actual time=1.376..1.376 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=27
24. 1.371 1.371 ↑ 360.0 1 1

CTE Scan on site_attrs (cost=0.00..7.20 rows=360 width=16) (actual time=1.369..1.371 rows=1 loops=1)

  • Buffers: shared hit=27
Planning time : 0.485 ms
Execution time : 15,871.662 ms