Why does PostgreSQL use a sequential scan instead of a bitmap index scan with my composite index?

  Kiến thức lập trình

I have a table called orders_table in postgres and I am creating composite index using two columns order_id and customer_id.

Query 1:

-- Create a new index on order_id and customer_id column
CREATE INDEX idx_orders_table_order_id_customer_id ON
    orders_table (order_id, customer_id);

When I analyze this query, postgres is running a Seq Scan on column customer_id

EXPLAIN ANALYZE
SELECT
    *
FROM
    orders_table
WHERE
    customer_id = 'VINET'

Output:

Seq Scan on orders_table  (cost=0.00..24.38 rows=5 width=90) (actual time=0.018..0.159 rows=5 loops=1)
  Filter: (customer_id = 'VINET'::bpchar)
  Rows Removed by Filter: 825
Planning Time: 1.160 ms
Execution Time: 0.179 ms

Query 2:

But when I reverse the order of columns in the index such as :

CREATE INDEX idx_orders_table_customer_id_order_id ON
    orders_table (customer_id, order_id);

and analyze the query:

EXPLAIN ANALYZE
SELECT
    *
FROM
    orders_table
WHERE
    customer_id = 'VINET'
Bitmap Heap Scan on orders_table  (cost=4.31..15.41 rows=5 width=90) (actual time=0.040..0.042 rows=5 loops=1)
  Recheck Cond: (customer_id = 'VINET'::bpchar)
  Heap Blocks: exact=2
  ->  Bitmap Index Scan on idx_orders_table_customer_id_order_id  (cost=0.00..4.31 rows=5 width=0) (actual time=0.036..0.036 rows=5 loops=1)
        Index Cond: (customer_id = 'VINET'::bpchar)
Planning Time: 1.269 ms
Execution Time: 0.066 ms

This time it is performing bit Bitmap Index Scan and not sequential scan. I understand that due to low cardinality of the column, it is favoring Bitmap Index Scan. But why did it not perform Bitmap Index Scan in the first query as well? Can someone help me understand this behavior?

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

Why does PostgreSQL use a sequential scan instead of a bitmap index scan with my composite index?

I have a table called orders_table in postgres and I am creating composite index using two columns order_id and customer_id.

Query 1:

-- Create a new index on order_id and customer_id column
CREATE INDEX idx_orders_table_order_id_customer_id ON
    orders_table (order_id, customer_id);

When I analyze this query, postgres is running a Seq Scan on column customer_id

EXPLAIN ANALYZE
SELECT
    *
FROM
    orders_table
WHERE
    customer_id = 'VINET'

Output:

Seq Scan on orders_table  (cost=0.00..24.38 rows=5 width=90) (actual time=0.018..0.159 rows=5 loops=1)
  Filter: (customer_id = 'VINET'::bpchar)
  Rows Removed by Filter: 825
Planning Time: 1.160 ms
Execution Time: 0.179 ms

Query 2:

But when I reverse the order of columns in the index such as :

CREATE INDEX idx_orders_table_customer_id_order_id ON
    orders_table (customer_id, order_id);

and analyze the query:

EXPLAIN ANALYZE
SELECT
    *
FROM
    orders_table
WHERE
    customer_id = 'VINET'
Bitmap Heap Scan on orders_table  (cost=4.31..15.41 rows=5 width=90) (actual time=0.040..0.042 rows=5 loops=1)
  Recheck Cond: (customer_id = 'VINET'::bpchar)
  Heap Blocks: exact=2
  ->  Bitmap Index Scan on idx_orders_table_customer_id_order_id  (cost=0.00..4.31 rows=5 width=0) (actual time=0.036..0.036 rows=5 loops=1)
        Index Cond: (customer_id = 'VINET'::bpchar)
Planning Time: 1.269 ms
Execution Time: 0.066 ms

This time it is performing bit Bitmap Index Scan and not sequential scan. I understand that due to low cardinality of the column, it is favoring Bitmap Index Scan. But why did it not perform Bitmap Index Scan in the first query as well? Can someone help me understand this behavior?

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT