explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zhNb

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=8,502,067.92..8,786,511.21 rows=37,000 width=24) (actual rows= loops=)

  • Group Key: ""user"".site_id, site_attrs.num_attrs
2.          

CTE active_site_ids

3. 0.000 0.000 ↓ 0.0

Limit (cost=737,229.95..737,230.20 rows=100 width=8) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=737,229.95..737,230.41 rows=185 width=8) (actual rows= loops=)

  • Sort Key: user_1.site_id
5. 0.000 0.000 ↓ 0.0

HashAggregate (cost=737,221.14..737,222.99 rows=185 width=8) (actual rows= loops=)

  • Group Key: user_1.site_id
6. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on "user" user_1 (cost=13,462.73..736,254.73 rows=386,564 width=8) (actual rows= loops=)

  • 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)))
  • Filter: (site_id > 0)
7. 0.000 0.000 ↓ 0.0

BitmapOr (cost=13,462.73..13,462.73 rows=386,676 width=0) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_user_created (cost=0.00..12,741.06 rows=378,050 width=0) (actual rows= loops=)

  • 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))
9. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_user_updated (cost=0.00..528.39 rows=8,626 width=0) (actual rows= loops=)

  • 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))
10.          

CTE site_attrs

11. 0.000 0.000 ↓ 0.0

Sort (cost=79.66..80.56 rows=360 width=16) (actual rows= loops=)

  • Sort Key: site_attribute.site_id
12. 0.000 0.000 ↓ 0.0

HashAggregate (cost=60.77..64.37 rows=360 width=16) (actual rows= loops=)

  • Group Key: site_attribute.site_id
13. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=3.25..58.29 rows=496 width=16) (actual rows= loops=)

  • Hash Cond: (site_attribute.site_id = active_site_ids_1.site_id)
14. 0.000 0.000 ↓ 0.0

Seq Scan on site_attribute (cost=0.00..44.84 rows=1,784 width=16) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

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

16. 0.000 0.000 ↓ 0.0

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

17. 0.000 0.000 ↓ 0.0

Sort (cost=7,764,757.16..7,835,775.49 rows=28,407,329 width=24) (actual rows= loops=)

  • Sort Key: ""user"".site_id, site_attrs.num_attrs
18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14.95..2,500,238.94 rows=28,407,329 width=24) (actual rows= loops=)

  • Hash Cond: ("user".site_id = site_attrs.site_id)
19. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=3.25..1,466,516.14 rows=15,781,849 width=16) (actual rows= loops=)

  • Hash Cond: ("user".site_id = active_site_ids.site_id)
20. 0.000 0.000 ↓ 0.0

Seq Scan on "user" (cost=0.00..1,214,299.21 rows=29,196,421 width=16) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

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

22. 0.000 0.000 ↓ 0.0

CTE Scan on active_site_ids (cost=0.00..2.00 rows=100 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

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

24. 0.000 0.000 ↓ 0.0

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