Index not used for query

Question

I'm running the following query, and the index I have created is not used; PostgreSQL uses a sequential scan instead of an index scan.

Query:

EXPLAIN (analyze, buffers)
SELECT  date, 
    company, 
    host
FROM    mytable
where   date < ( now() - INTERVAL '10 days' )

The index I have created:

CREATE INDEX ix_mytable_company ON mytable USING btree (
    host,
    cumpany,
    date
)





thanks for the reply but i still have problems

get this no matter what i try:

Seq Scan on mytable (cost=0.00..5577644.22 rows=65056015 width=34) (actual time=120.238..43372.108 rows=63058234 loops=1) Filter: (date < (now() - '10 days'::interval)) Rows Removed by Filter: 2543550 Buffers: shared hit=581914 read=3847699 Planning Time: 1.263 ms JIT: Functions: 4 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.495 ms, Inlining 66.265 ms, Optimization 37.358 ms, Emission 16.325 ms, Total 120.443 ms Execution Time: 46204.494 ms

view index shows this CREATE INDEX ix_mytable_company ON public.mytable USING btree (date) INCLUDE (host, cumpany)

and i still run: EXPLAIN (analyze, buffers) SELECT date,cumpany,host FROM mytable where date < (now() - INTERVAL '10 days' )

6.5 milj rows should qualify for an index scan i think.

Addition to @VasylMoskalov comment: How many rows in your table? If there are small number of rows the planner may choose to seq scan table because it's faster than 2 read operations: read from index and from table — Dmitry K.
– Dmitry K., Commented Dec 22, 2022 at 8:20
@DmitryK. I think that the planner even will not try touse this index 'cause the date field is the last in the index definition. So, if where will be like 'host=val and cumpany=val1 and date<....) then, may be, index will be used. — Vasyl Moskalov
– Vasyl Moskalov, Commented Dec 22, 2022 at 8:25

Laurenz Albe · Accepted Answer · 2022-12-22 08:25:53Z

2

The problem is that date is not the first column in the index, so the index cannot be used for the query. It would be enough to have an index on date alone, but if you are targeting an index-only scan, you could use this index:

CREATE INDEX myindex ON mytable (date) INCLUDE (company, host);

/* for good performance, refresh the visibility map */
VACUUM mytable;

Note that an index will only be used if the table is big enough.

answered Dec 22, 2022 at 8:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

wiper Over a year ago

if i use where date < ( now() - INTERVAL '10 days' ) -- sequential scan occur where date > ( now() - INTERVAL '10 days' ) -- indesx scan occur what could explain this behavior, index sort order?

Laurenz Albe Over a year ago

@wiper That would mean that so many rows satisfy the condition that PostgreSQL decides to do a sequential scan.

Collectives™ on Stack Overflow

Index not used for query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related