explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n4eK

Settings
# exclusive inclusive rows x rows loops node
1. 195.635 544,543.010 ↓ 214.3 23,148 1

Nested Loop Semi Join (cost=90,641.95..300,751.07 rows=108 width=8) (actual time=286,486.679..544,543.010 rows=23,148 loops=1)

  • Buffers: shared hit=2,320,855 read=496,242
2. 71.670 535,433.007 ↓ 11.5 278,574 1

Nested Loop (cost=90,641.26..263,193.34 rows=24,134 width=16) (actual time=286,477.988..535,433.007 rows=278,574 loops=1)

  • Buffers: shared hit=887,067 read=489,606
3. 351.761 286,594.755 ↓ 11.9 278,574 1

HashAggregate (cost=90,640.82..90,875.78 rows=23,496 width=8) (actual time=286,474.539..286,594.755 rows=278,574 loops=1)

  • Group Key: prop_1.node_id
  • Buffers: shared hit=4 read=260,851
4. 283,652.297 286,242.994 ↓ 11.3 278,574 1

Bitmap Heap Scan on alf_node_properties prop_1 (cost=1,144.39..90,579.43 rows=24,556 width=8) (actual time=2,604.290..286,242.994 rows=278,574 loops=1)

  • Recheck Cond: ((286 = qname_id) AND ((string_value)::text = 'Action and Adventure'::text))
  • Rows Removed by Index Recheck: 11,634,903
  • Heap Blocks: exact=55,951 lossy=202,820
  • Buffers: shared hit=4 read=260,851
5. 2,590.697 2,590.697 ↓ 11.3 278,574 1

Bitmap Index Scan on idx_alf_nprop_s (cost=0.00..1,138.25 rows=24,556 width=0) (actual time=2,590.697..2,590.697 rows=278,574 loops=1)

  • Index Cond: ((286 = qname_id) AND ((string_value)::text = 'Action and Adventure'::text))
  • Buffers: shared hit=4 read=2,080
6. 248,766.582 248,766.582 ↑ 1.0 1 278,574

Index Scan using alf_node_pkey on alf_node node (cost=0.43..7.35 rows=1 width=8) (actual time=0.893..0.893 rows=1 loops=278,574)

  • Index Cond: (id = prop_1.node_id)
  • Filter: ((type_qname_id <> 148) AND (store_id = 12) AND (type_qname_id = ANY ('{51,103,198,199,205,212}'::bigint[])))
  • Buffers: shared hit=887,063 read=228,755
7. 8,914.368 8,914.368 ↓ 0.0 0 278,574

Index Only Scan using idx_alf_nprop_s on alf_node_properties prop (cost=0.69..1.56 rows=1 width=8) (actual time=0.032..0.032 rows=0 loops=278,574)

  • Index Cond: ((qname_id = 287) AND (string_value = 'Hachette Livre'::text) AND (node_id = node.id))
  • Heap Fetches: 23,148
  • Buffers: shared hit=1,433,788 read=6,636
Planning time : 92.676 ms
Execution time : 544,550.894 ms