[sgarland]

This (index ordering resulting in useless indices) is not true, at least not in newer versions of Postgres (I just tried with 15).

While the query will take much longer (for me, it was about 47 msec vs 0.6 msec for 2,000,000 rows), it still uses the index.

Similarly, while normally you wouldn’t expect a query using predicate Y to use an index defined on columns (X, Y, Z) if predicate X is also not referenced, Postgres may choose to do so, depending on table stats, and the relative difference between random_page_cost and seq_page_cost. I’ve seen it happen before.

[ghusbands]

> This (index ordering resulting in useless indices) is not true, at least not in newer versions of Postgres (I just tried with 15).

> While the query will take much longer (for me, it was about 47 msec vs 0.6 msec for 2,000,000 rows), it still uses the index.

I'd argue that something being 78x slower can make it pretty useless, but it is indeed at least used, in some cases.

[sgarland]

Certainly I wouldn’t encourage this, no, but it is possible, and is still usually faster than a sequential scan.