explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hzyO

Settings
# exclusive inclusive rows x rows loops node
1. 0.222 6,464.931 ↑ 1.0 100 1

Limit (cost=484.35..66,721.24 rows=100 width=185) (actual time=158.449..6,464.931 rows=100 loops=1)

2.          

CTE input_cte

3. 0.039 137.843 ↑ 94.0 5 1

Bitmap Heap Scan on input (cost=14.12..483.35 rows=470 width=25) (actual time=137.827..137.843 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.804 ↓ 0.0 0 1

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

5. 38.878 38.878 ↓ 0.0 0 1

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

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

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

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

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

  • Index Cond: (input ~~ 'www.abortionno.org%'::text)
8. 2,542.404 6,464.709 ↑ 4,978.7 100 1

Nested Loop (cost=1.00..329,772,944.56 rows=497,869 width=185) (actual time=158.447..6,464.709 rows=100 loops=1)

  • Join Filter: (measurement.input_no = input_cte.input_no)
  • Rows Removed by Join Filter: 2298279
9. 766.349 1,623.925 ↑ 13.2 459,676 1

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

10. 190.270 190.270 ↑ 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.127..190.270 rows=10,763 loops=1)

  • Filter: (probe_cc = 'BR'::bpchar)
  • Rows Removed by Filter: 301893
11. 667.306 667.306 ↑ 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.009..0.062 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 input_cte (cost=0.00..9.40 rows=470 width=36) (actual time=0.001..0.005 rows=5 loops=459,676)

Planning time : 0.765 ms
Execution time : 6,465.256 ms