explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VAkq : q19-source

Settings
# exclusive inclusive rows x rows loops node
1. 0.202 101.694 ↑ 1.0 1 1

Aggregate (cost=64,771.63..64,771.64 rows=1 width=12) (actual time=101.694..101.694 rows=1 loops=1)

2. 0.397 101.492 ↓ 1.0 121 1

Nested Loop (cost=3,224.27..64,770.76 rows=116 width=12) (actual time=23.326..101.492 rows=121 loops=1)

3. 11.306 33.680 ↑ 1.0 485 1

Bitmap Heap Scan on part (cost=3,223.84..5,653.41 rows=495 width=30) (actual time=22.524..33.680 rows=485 loops=1)

  • Recheck Cond: (((p_brand = 'Brand#12'::bpchar) AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand = 'Brand#23'::bpchar) AND (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (p_size <= 10) AND (p_size >= 1)) OR ((p_brand = 'Brand#34'::bpchar) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[]))))
  • Filter: ((p_size >= 1) AND (((p_brand = 'Brand#12'::bpchar) AND (p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (p_size <= 5)) OR ((p_brand = 'Brand#23'::bpchar) AND (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (p_size <= 10)) OR ((p_brand = 'Brand#34'::bpchar) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (p_size <= 15))))
  • Rows Removed by Filter: 535
  • Heap Blocks: exact=906
4. 0.127 22.374 ↓ 0.0 0 1

BitmapOr (cost=3,223.84..3,223.84 rows=1,045 width=0) (actual time=22.374..22.374 rows=0 loops=1)

5. 0.435 9.098 ↓ 0.0 0 1

BitmapAnd (cost=1,062.29..1,062.29 rows=83 width=0) (actual time=9.098..9.098 rows=0 loops=1)

6. 2.341 2.341 ↑ 1.0 8,167 1

Bitmap Index Scan on part_idx_3 (cost=0.00..195.72 rows=8,440 width=0) (actual time=2.341..2.341 rows=8,167 loops=1)

  • Index Cond: (p_brand = 'Brand#12'::bpchar)
7. 3.400 3.400 ↓ 1.0 20,271 1

Bitmap Index Scan on part_idx_5 (cost=0.00..425.02 rows=20,060 width=0) (actual time=3.400..3.400 rows=20,271 loops=1)

  • Index Cond: ((p_size <= 5) AND (p_size >= 1))
8. 2.922 2.922 ↓ 1.0 19,906 1

Bitmap Index Scan on part_idx_6 (cost=0.00..440.68 rows=19,600 width=0) (actual time=2.922..2.922 rows=19,906 loops=1)

  • Index Cond: (p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[]))
9. 0.416 8.657 ↓ 0.0 0 1

BitmapAnd (cost=1,516.75..1,516.75 rows=172 width=0) (actual time=8.657..8.657 rows=0 loops=1)

10. 1.219 1.219 ↑ 1.1 7,870 1

Bitmap Index Scan on part_idx_3 (cost=0.00..195.77 rows=8,447 width=0) (actual time=1.219..1.219 rows=7,870 loops=1)

  • Index Cond: (p_brand = 'Brand#23'::bpchar)
11. 2.960 2.960 ↓ 1.0 19,986 1

Bitmap Index Scan on part_idx_6 (cost=0.00..458.69 rows=19,867 width=0) (actual time=2.960..2.960 rows=19,986 loops=1)

  • Index Cond: (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[]))
12. 4.062 4.062 ↑ 1.0 40,474 1

Bitmap Index Scan on part_idx_5 (cost=0.00..861.42 rows=40,900 width=0) (actual time=4.062..4.062 rows=40,474 loops=1)

  • Index Cond: ((p_size <= 10) AND (p_size >= 1))
13. 0.334 4.492 ↓ 0.0 0 1

BitmapAnd (cost=644.29..644.29 rows=790 width=0) (actual time=4.492..4.492 rows=0 loops=1)

14. 1.187 1.187 ↓ 1.0 8,014 1

Bitmap Index Scan on part_idx_3 (cost=0.00..183.42 rows=7,867 width=0) (actual time=1.187..1.187 rows=8,014 loops=1)

  • Index Cond: (p_brand = 'Brand#34'::bpchar)
15. 2.971 2.971 ↑ 1.0 20,080 1

Bitmap Index Scan on part_idx_6 (cost=0.00..460.37 rows=20,093 width=0) (actual time=2.971..2.971 rows=20,080 loops=1)

  • Index Cond: (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[]))
16. 67.415 67.415 ↓ 0.0 0 485

Index Scan using lineitem_idx_1 on lineitem (cost=0.43..119.42 rows=1 width=21) (actual time=0.125..0.139 rows=0 loops=485)

  • Index Cond: (l_partkey = part.p_partkey)
  • Filter: ((l_shipmode = ANY ('{AIR,"AIR REG"}'::bpchar[])) AND (l_shipinstruct = 'DELIVER IN PERSON'::bpchar) AND (((l_quantity >= '1'::numeric) AND (l_quantity <= '11'::numeric)) OR ((l_quantity >= '10'::numeric) AND (l_quantity <= '20'::numeric)) OR ((l_quantity >= '20'::numeric) AND (l_quantity <= '30'::numeric))) AND (((part.p_brand = 'Brand#12'::bpchar) AND (part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (l_quantity >= '1'::numeric) AND (l_quantity <= '11'::numeric) AND (part.p_size <= 5)) OR ((part.p_brand = 'Brand#23'::bpchar) AND (part.p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (l_quantity >= '10'::numeric) AND (l_quantity <= '20'::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#34'::bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (l_quantity >= '20'::numeric) AND (l_quantity <= '30'::numeric) AND (part.p_size <= 15))))
  • Rows Removed by Filter: 29
Planning time : 6.240 ms
Execution time : 102.122 ms