explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1pg

Settings
# exclusive inclusive rows x rows loops node
1. 9,063.961 52,832.012 ↑ 374.0 100 1

GroupAggregate (cost=8,393,197.50..8,674,699.53 rows=37,400 width=24) (actual time=40,799.463..52,832.012 rows=100 loops=1)

  • Group Key: ""user"".site_id, site_attrs.num_attrs
  • Buffers: shared hit=1017874 read=74394, temp read=315073 written=315672
2.          

CTE active_site_ids

3. 0.012 1,372.544 ↑ 1.0 100 1

Limit (cost=696,571.94..696,572.19 rows=100 width=8) (actual time=1,372.529..1,372.544 rows=100 loops=1)

  • Buffers: shared hit=169004
4. 0.050 1,372.532 ↑ 1.9 100 1

Sort (cost=696,571.94..696,572.41 rows=187 width=8) (actual time=1,372.527..1,372.532 rows=100 loops=1)

  • Sort Key: user_1.site_id
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=169004
5. 86.280 1,372.482 ↑ 1.1 171 1

HashAggregate (cost=696,563.01..696,564.88 rows=187 width=8) (actual time=1,372.463..1,372.482 rows=171 loops=1)

  • Group Key: user_1.site_id
  • Buffers: shared hit=169004
6. 1,233.559 1,286.202 ↓ 1.1 380,640 1

Bitmap Heap Scan on "user" user_1 (cost=12,094.34..695,695.06 rows=347,182 width=8) (actual time=64.560..1,286.202 rows=380,640 loops=1)

  • Recheck Cond: (((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 IS NOT NULL) AND (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: 2988244
  • Filter: (site_id > 0)
  • Heap Blocks: exact=60942 lossy=102052
  • Buffers: shared hit=169004
7. 0.003 52.643 ↓ 0.0 0 1

BitmapOr (cost=12,094.34..12,094.34 rows=347,270 width=0) (actual time=52.643..52.643 rows=0 loops=1)

  • Buffers: shared hit=6010
8. 44.660 44.660 ↓ 1.1 367,093 1

Bitmap Index Scan on ix_user_created (cost=0.00..11,453.56 rows=339,700 width=0) (actual time=44.660..44.660 rows=367,093 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
9. 7.980 7.980 ↓ 8.2 61,867 1

Bitmap Index Scan on ix_user_updated (cost=0.00..467.19 rows=7,570 width=0) (actual time=7.980..7.980 rows=61,867 loops=1)

  • Index Cond: ((updated IS NOT NULL) AND (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
10.          

CTE site_attrs

11. 0.032 0.562 ↑ 3.6 100 1

Sort (cost=79.55..80.45 rows=360 width=16) (actual time=0.557..0.562 rows=100 loops=1)

  • Sort Key: site_attribute.site_id
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=27
12. 0.119 0.530 ↑ 3.6 100 1

HashAggregate (cost=60.66..64.26 rows=360 width=16) (actual time=0.517..0.530 rows=100 loops=1)

  • Group Key: site_attribute.site_id
  • Buffers: shared hit=27
13. 0.224 0.411 ↓ 1.3 656 1

Hash Semi Join (cost=3.25..58.19 rows=494 width=16) (actual time=0.036..0.411 rows=656 loops=1)

  • Hash Cond: (site_attribute.site_id = active_site_ids_1.site_id)
  • Buffers: shared hit=27
14. 0.169 0.169 ↑ 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.169 rows=1,778 loops=1)

  • Buffers: shared hit=27
15. 0.011 0.018 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=8) (actual time=0.018..0.018 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
16. 0.007 0.007 ↑ 1.0 100 1

CTE Scan on active_site_ids active_site_ids_1 (cost=0.00..2.00 rows=100 width=8) (actual time=0.000..0.007 rows=100 loops=1)

17. 22,031.465 43,768.051 ↑ 1.4 19,628,922 1

Sort (cost=7,696,544.86..7,766,826.87 rows=28,112,803 width=24) (actual time=40,575.261..43,768.051 rows=19,628,922 loops=1)

  • Sort Key: ""user"".site_id, site_attrs.num_attrs
  • Sort Method: external merge Disk: 653088kB
  • Buffers: shared hit=1017874 read=74394, temp read=286770 written=287276
18. 5,467.063 21,736.586 ↑ 1.4 19,628,922 1

Hash Left Join (cost=14.95..2,488,725.11 rows=28,112,803 width=24) (actual time=1,373.226..21,736.586 rows=19,628,922 loops=1)

  • Hash Cond: ("user".site_id = site_attrs.site_id)
  • Buffers: shared hit=1017874 read=74394
19. 7,716.926 16,268.925 ↓ 1.3 19,628,922 1

Hash Semi Join (cost=3.25..1,465,719.74 rows=15,618,224 width=16) (actual time=1,372.609..16,268.925 rows=19,628,922 loops=1)

  • Hash Cond: ("user".site_id = active_site_ids.site_id)
  • Buffers: shared hit=1017847 read=74394
20. 7,179.421 7,179.421 ↑ 1.0 29,170,779 1

Seq Scan on "user" (cost=0.00..1,215,297.79 rows=29,206,079 width=16) (actual time=0.021..7,179.421 rows=29,170,779 loops=1)

  • Buffers: shared hit=848843 read=74394
21. 0.017 1,372.578 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=8) (actual time=1,372.578..1,372.578 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=169004
22. 1,372.561 1,372.561 ↑ 1.0 100 1

CTE Scan on active_site_ids (cost=0.00..2.00 rows=100 width=8) (actual time=1,372.530..1,372.561 rows=100 loops=1)

  • Buffers: shared hit=169004
23. 0.017 0.598 ↑ 3.6 100 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=27
24. 0.581 0.581 ↑ 3.6 100 1

CTE Scan on site_attrs (cost=0.00..7.20 rows=360 width=16) (actual time=0.558..0.581 rows=100 loops=1)

  • Buffers: shared hit=27
Planning time : 0.435 ms
Execution time : 52,951.217 ms