explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C4bL

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

GroupAggregate (cost=7,310,694.71..7,573,865.55 rows=37,000 width=24) (actual rows= loops=)

  • Group Key: u.site_id, site_attrs.num_attrs
2.          

CTE active_site_ids

3. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on "user" (cost=27,721.93..45,025.36 rows=4,540 width=8) (actual rows= loops=)

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

BitmapAnd (cost=27,721.93..27,721.93 rows=4,547 width=0) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_user_site_id (cost=0.00..11,755.37 rows=306,681 width=0) (actual rows= loops=)

  • Index Cond: ((site_id >= 0) AND (site_id <= 100))
6. 0.000 0.000 ↓ 0.0

BitmapOr (cost=15,965.17..15,965.17 rows=432,977 width=0) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_user_created (cost=0.00..12,741.54 rows=378,098 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))
8. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_user_updated (cost=0.00..3,221.36 rows=54,880 width=0) (actual rows= loops=)

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

CTE site_attrs

10. 0.000 0.000 ↓ 0.0

Sort (cost=189.44..190.34 rows=360 width=16) (actual rows= loops=)

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

HashAggregate (cost=170.56..174.16 rows=360 width=16) (actual rows= loops=)

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

Hash Join (cost=106.65..166.10 rows=892 width=16) (actual rows= loops=)

  • Hash Cond: (site_attribute.site_id = active_site_ids.site_id)
13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

Hash (cost=104.15..104.15 rows=200 width=8) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=102.15..104.15 rows=200 width=8) (actual rows= loops=)

  • Group Key: active_site_ids.site_id
16. 0.000 0.000 ↓ 0.0

CTE Scan on active_site_ids (cost=0.00..90.80 rows=4,540 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=7,265,479.01..7,331,179.22 rows=26,280,084 width=24) (actual rows= loops=)

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

Hash Left Join (cost=118.35..2,409,949.05 rows=26,280,084 width=24) (actual rows= loops=)

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

Hash Join (cost=106.65..1,453,634.34 rows=14,600,046 width=16) (actual rows= loops=)

  • Hash Cond: (u.site_id = sites.site_id)
20. 0.000 0.000 ↓ 0.0

Seq Scan on "user" u (cost=0.00..1,214,451.93 rows=29,200,093 width=16) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=104.15..104.15 rows=200 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

HashAggregate (cost=102.15..104.15 rows=200 width=8) (actual rows= loops=)

  • Group Key: sites.site_id
23. 0.000 0.000 ↓ 0.0

CTE Scan on active_site_ids sites (cost=0.00..90.80 rows=4,540 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

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

25. 0.000 0.000 ↓ 0.0

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