explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TlNO

Settings
# exclusive inclusive rows x rows loops node
1. 309.493 98,207.799 ↑ 6.7 30 1

GroupAggregate (cost=55,509,089.62..55,519,037.72 rows=200 width=16) (actual time=97,858.188..98,207.799 rows=30 loops=1)

  • Group Key: (generate_series(date_trunc('day'::text, date_trunc('day'::text, (now() - '29 days'::interval))), now(), ('1 day'::cstring)::interval))
2. 235.367 97,898.306 ↑ 6.3 212,134 1

Sort (cost=55,509,089.62..55,512,404.99 rows=1,326,147 width=45) (actual time=97,841.556..97,898.306 rows=212,134 loops=1)

  • Sort Key: (generate_series(date_trunc('day'::text, date_trunc('day'::text, (now() - '29 days'::interval))), now(), ('1 day'::cstring)::interval))
  • Sort Method: external sort Disk: 11416kB
3. 71,749.515 97,662.939 ↑ 6.3 212,134 1

Nested Loop Left Join (cost=14,475.87..55,292,636.36 rows=1,326,147 width=45) (actual time=15,698.036..97,662.939 rows=212,134 loops=1)

  • Join Filter: (tsrange(timezone('America/Los_Angeles'::text, (generate_series(date_trunc('day'::text, date_trunc('day'::text, (now() - '29 days'::interval))), now(), ('1 day'::cstring)::interval))), (timezone('America/Los_Angeles'::text, (generate_series(date_trunc('day'::text, date_trunc('day'::text, (now() - '29 days'::interval))), now(), ('1 day'::cstring)::interval))) + ('1 day'::cstring)::interval)) @> timezone('America/Los_Angeles'::text, pages."timestamp"))
  • Rows Removed by Join Filter: 40418757
4. 0.114 0.114 ↑ 33.3 30 1

Result (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.013..0.114 rows=30 loops=1)

5. 11,489.987 25,913.310 ↓ 1.0 1,354,363 30

Materialize (cost=14,475.87..551,815.21 rows=1,326,147 width=45) (actual time=20.277..863.777 rows=1,354,363 loops=30)

6. 13,823.753 14,423.323 ↓ 1.0 1,354,363 1

Bitmap Heap Scan on pages (cost=14,475.87..533,528.47 rows=1,326,147 width=45) (actual time=608.158..14,423.323 rows=1,354,363 loops=1)

  • Recheck Cond: ((path ~~* '/book/%'::text) OR (path ~~* '/booking/%'::text) OR (path ~~* '/wedding/%'::text) OR (path ~~* '/picturepeople/%'::text))
  • Rows Removed by Index Recheck: 2896025
  • Heap Blocks: exact=41234 lossy=421419
7. 0.003 599.570 ↓ 0.0 0 1

BitmapOr (cost=14,475.87..14,475.87 rows=1,360,230 width=0) (actual time=599.570..599.570 rows=0 loops=1)

8. 466.452 466.452 ↓ 1.1 1,400,703 1

Bitmap Index Scan on trgm_idx_javascript_pages_path (cost=0.00..11,163.85 rows=1,226,113 width=0) (actual time=466.452..466.452 rows=1,400,703 loops=1)

  • Index Cond: (path ~~* '/book/%'::text)
9. 78.447 78.447 ↑ 1.0 85,764 1

Bitmap Index Scan on trgm_idx_javascript_pages_path (cost=0.00..993.11 rows=87,615 width=0) (actual time=78.447..78.447 rows=85,764 loops=1)

  • Index Cond: (path ~~* '/booking/%'::text)
10. 11.480 11.480 ↓ 50.6 10,373 1

Bitmap Index Scan on trgm_idx_javascript_pages_path (cost=0.00..265.54 rows=205 width=0) (actual time=11.480..11.480 rows=10,373 loops=1)

  • Index Cond: (path ~~* '/wedding/%'::text)
11. 43.188 43.188 ↓ 1.2 55,631 1

Bitmap Index Scan on trgm_idx_javascript_pages_path (cost=0.00..727.23 rows=46,297 width=0) (actual time=43.188..43.188 rows=55,631 loops=1)

  • Index Cond: (path ~~* '/picturepeople/%'::text)