PostgreSQL not using index-only scan

Question

I'm using Postgres 12. I have a sales_history table with 999900 rows. Columns in the table include:

order_id numeric NOT NULL,
product_id numeric NOT NULL,
customer_id numeric NOT NULL,
sales_person_id numeric,
quantity numeric NOT NULL,
unit_price numeric,
sale_amount numeric,
sales_date date,
total_amount numeric,
CONSTRAINT sales_history_pkey PRIMARY KEY (order_id)

My sample data:

I have this query:

select sales_date
from sales_history
where (quantity * unit_price) between 5000000 and 10000000

I create an index for the (quantity * unit_price) and column sales_date, and I expect the query to use index-only scan

create index idx_sale_diff on sales_history( (quantity * unit_price), sales_date )

However, the query use bitmap scan instead:

I tried to VACUUM and ANALYZE but it's still bitmap scan

Can someone explain why this query did not use the index-only scan ? As I understand, all the columns needed for the query are available in the index so there's no need to do the bitmap scan

Thanks in advance :D

Laurenz Albe · Accepted Answer · 2020-05-20 07:47:40Z

1

Probably there are not enough blocks marked “all visible” in the visibility map of the table, so that PostgreSQL has to visit the table in many cases to determine if the row is visible or not.

The solution is

VACUUM sales_history;

which will update the visibility map.

You have to VACUUM a table regularly to get index only scans. With INSERT-only tables, that does not happen automatically in PostgreSQL versions below v13.

answered May 20, 2020 at 7:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL not using index-only scan

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related