Search Postgresql Archives

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

Subject: Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
Date: Mon, 6 Dec 2021 23:06:46 -0500
In-reply-to: <82b3a0d97f5ee6279f1c7e6048c510b07f0b313b.camel@cybertec.at>
References: <bc47e9d0c490942eebb01dc1129097012005e1d2.camel@lists.simkin.ca> <CA+bJJbxT55278A6B3vUKHgNR-aSDd6YWbSvKb_KMNO9c6ZEbGQ@mail.gmail.com> <CAFj8pRDsmF2PVKOQK47EhOfhSPrKuRoUSfb=cwJJSM+NubbUMg@mail.gmail.com> <82b3a0d97f5ee6279f1c7e6048c510b07f0b313b.camel@cybertec.at>
User-agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.3.0

On 12/6/21 22:16, Laurenz Albe wrote:

An ugly alternative is to use "ORDER BY id + 0", which prevents PostgreSQL
from using the index.

That was actually the earliest form of Oracle hints. I remember doingexactly that in Oracle 5.1.22 on VAX/VMS.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

References:
- Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
  - From: Alan Hodgson
- Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
  - From: Francisco Olarte
- Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
  - From: Pavel Stehule
- Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
  - From: Laurenz Albe