explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sqck

Settings
# exclusive inclusive rows x rows loops node
1. 1,321.426 134,992.859 ↓ 17,318.2 2,199,411 1

Nested Loop Semi Join (cost=614,415.07..768,677.18 rows=127 width=8) (actual time=54,992.034..134,992.859 rows=2,199,411 loops=1)

2. 6,071.924 83,084.980 ↓ 807.4 2,199,411 1

Hash Join (cost=614,414.50..747,442.21 rows=2,724 width=24) (actual time=54,987.345..83,084.980 rows=2,199,411 loops=1)

  • Hash Cond: (node.id = prop_1.node_id)
3. 20,613.375 59,493.463 ↓ 37.8 2,199,411 1

Hash Join (cost=201,368.65..334,158.46 rows=58,220 width=16) (actual time=37,450.687..59,493.463 rows=2,199,411 loops=1)

  • Hash Cond: (node.id = prop_2.node_id)
4. 1,429.698 1,429.698 ↓ 1.0 2,200,170 1

Seq Scan on alf_node node (cost=0.00..124,123.62 rows=2,192,478 width=8) (actual time=0.037..1,429.698 rows=2,200,170 loops=1)

  • Filter: ((type_qname_id <> '149'::bigint) AND (store_id = '6'::bigint) AND (type_qname_id = '361'::bigint))
  • Rows Removed by Filter: 7280
5. 356.196 37,450.390 ↓ 49.3 2,199,427 1

Hash (cost=200,810.86..200,810.86 rows=44,623 width=8) (actual time=37,450.389..37,450.390 rows=2,199,427 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 44035kB
6. 2,700.284 37,094.194 ↓ 49.3 2,199,427 1

HashAggregate (cost=200,364.63..200,810.86 rows=44,623 width=8) (actual time=36,100.330..37,094.194 rows=2,199,427 loops=1)

  • Group Key: prop_2.node_id
7. 12,520.456 34,393.910 ↓ 37.6 2,199,427 1

Bitmap Heap Scan on alf_node_properties prop_2 (cost=3,456.03..200,218.48 rows=58,460 width=8) (actual time=21,884.896..34,393.910 rows=2,199,427 loops=1)

  • Recheck Cond: (('504'::bigint = qname_id) AND ((string_value)::text = 'Contabilitate financiara'::text))
  • Rows Removed by Index Recheck: 31015319
  • Heap Blocks: exact=31299 lossy=582528
8. 21,873.454 21,873.454 ↓ 37.6 2,199,444 1

Bitmap Index Scan on idx_alf_nprop_s (cost=0.00..3,441.42 rows=58,460 width=0) (actual time=21,873.454..21,873.454 rows=2,199,444 loops=1)

  • Index Cond: (('504'::bigint = qname_id) AND ((string_value)::text = 'Contabilitate financiara'::text))
9. 445.410 17,519.593 ↓ 29.7 2,199,427 1

Hash (cost=412,120.79..412,120.79 rows=74,005 width=8) (actual time=17,519.593..17,519.593 rows=2,199,427 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 32 (originally 1) Memory Usage: 3715kB
10. 398.810 17,074.183 ↓ 29.7 2,199,427 1

Unique (cost=411,468.59..412,120.79 rows=74,005 width=8) (actual time=16,348.913..17,074.183 rows=2,199,427 loops=1)

11. 1,955.340 16,675.373 ↓ 16.9 2,199,427 1

Sort (cost=411,468.59..411,794.69 rows=130,441 width=8) (actual time=16,348.911..16,675.373 rows=2,199,427 loops=1)

  • Sort Key: prop_1.node_id
  • Sort Method: external sort Disk: 38704kB
12. 7,868.110 14,720.033 ↓ 16.9 2,199,427 1

Bitmap Heap Scan on alf_node_properties prop_1 (cost=6,161.59..400,385.64 rows=130,441 width=8) (actual time=6,858.962..14,720.033 rows=2,199,427 loops=1)

  • Recheck Cond: (('450'::bigint = qname_id) AND (long_value = '2'::bigint))
  • Rows Removed by Index Recheck: 31270253
  • Heap Blocks: exact=26212 lossy=582581
13. 6,851.923 6,851.923 ↓ 16.9 2,199,444 1

Bitmap Index Scan on idx_alf_nprop_l (cost=0.00..6,128.98 rows=130,441 width=0) (actual time=6,851.923..6,851.923 rows=2,199,444 loops=1)

  • Index Cond: (('450'::bigint = qname_id) AND (long_value = '2'::bigint))
14. 50,586.453 50,586.453 ↑ 1.0 1 2,199,411

Index Only Scan using idx_alf_nprop_l on alf_node_properties prop (cost=0.57..7.84 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=2,199,411)

  • Index Cond: ((qname_id = '510'::bigint) AND (long_value = '2'::bigint) AND (node_id = node.id))
  • Heap Fetches: 2199411