explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e6M3

Settings
# exclusive inclusive rows x rows loops node
1. 0.203 7,668.545 ↑ 1.0 100 1

Limit (cost=484.35..66,721.24 rows=100 width=181) (actual time=156.903..7,668.545 rows=100 loops=1)

2.          

CTE url

3. 0.041 137.510 ↑ 94.0 5 1

Bitmap Heap Scan on input (cost=14.12..483.35 rows=470 width=25) (actual time=137.492..137.510 rows=5 loops=1)

  • Recheck Cond: ((input ~~ 'https://www.abortionno.org%'::text) OR (input ~~ 'http://www.abortionno.org%'::text) OR (input ~~ 'www.abortionno.org%'::text))
  • Heap Blocks: exact=5
4. 0.007 137.469 ↓ 0.0 0 1

BitmapOr (cost=14.12..14.12 rows=470 width=0) (actual time=137.469..137.469 rows=0 loops=1)

5. 37.686 37.686 ↓ 0.0 0 1

Bitmap Index Scan on input_input_trgm_idx (cost=0.00..4.59 rows=157 width=0) (actual time=37.686..37.686 rows=0 loops=1)

  • Index Cond: (input ~~ 'https://www.abortionno.org%'::text)
6. 48.732 48.732 ↑ 52.3 3 1

Bitmap Index Scan on input_input_trgm_idx (cost=0.00..4.59 rows=157 width=0) (actual time=48.731..48.732 rows=3 loops=1)

  • Index Cond: (input ~~ 'http://www.abortionno.org%'::text)
7. 51.044 51.044 ↑ 31.4 5 1

Bitmap Index Scan on input_input_trgm_idx (cost=0.00..4.59 rows=157 width=0) (actual time=51.043..51.044 rows=5 loops=1)

  • Index Cond: (input ~~ 'www.abortionno.org%'::text)
8. 2,527.089 7,668.342 ↑ 4,978.7 100 1

Nested Loop (cost=1.00..329,772,944.56 rows=497,869 width=181) (actual time=156.899..7,668.342 rows=100 loops=1)

  • Join Filter: (measurement.input_no = url.input_no)
  • Rows Removed by Join Filter: 2298279
9. 769.486 2,842.873 ↑ 13.2 459,676 1

Nested Loop (cost=1.00..265,509,997.31 rows=6,076,874 width=149) (actual time=0.799..2,842.873 rows=459,676 loops=1)

10. 501.989 501.989 ↑ 17.0 10,763 1

Index Scan Backward using report_test_start_time_idx on report (cost=0.43..5,829,449.54 rows=183,436 width=97) (actual time=0.101..501.989 rows=10,763 loops=1)

  • Filter: (probe_cc = 'BR'::bpchar)
  • Rows Removed by Filter: 301893
11. 1,571.398 1,571.398 ↑ 45.7 43 10,763

Index Scan using measurement_v2_report_no_idx on measurement (cost=0.57..1,396.01 rows=1,964 width=52) (actual time=0.050..0.146 rows=43 loops=10,763)

  • Index Cond: (report_no = report.report_no)
  • Filter: (measurement_start_time < '2019-09-05 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 0
12. 2,298.380 2,298.380 ↑ 94.0 5 459,676

CTE Scan on url (cost=0.00..9.40 rows=470 width=36) (actual time=0.001..0.005 rows=5 loops=459,676)

Planning time : 0.753 ms
Execution time : 7,668.848 ms