agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Handsteiner <martin.handsteiner@sibvisions.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan
Date: Mon, 29 Jun 2020 10:30:19 -0400
Message-ID: <323666.1593441019@sss.pgh.pa.us> (raw)
In-Reply-To: <VI1PR1001MB1423793F6E5624602BD2DEAFE86E0@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
References: <VI1PR1001MB1423F5361320BB854708FD2FE8930@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
<CANP8+jLYmta=WfEpbP-DihtNkD7VKhbEzMyOkYUjuBCvWEZfdw@mail.gmail.com>
<VI1PR1001MB1423793F6E5624602BD2DEAFE86E0@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
Martin Handsteiner <martin.handsteiner@sibvisions.com> writes:
> yes, this would exactly be the feature I was talking about.
> enable_costlimit = true (default) | false
> How high is the possibility, that this feature will find the way into the postgres db?
Not very good, unless you can present a far more convincing use-case.
If I understand your desire, it is that issuing the "same" query in
different transactions would generate identical overall results
despite varying the offset/limit so as to fetch different parts of
that unchanging result.
The problem with this is that whether changing the offset/limit
changes the plan shape is just one of many reasons why you might
not get a consistent result --- the most unavoidable being that
other transactions might commit data changes.
Moreover, you insist that you shouldn't have to use an ORDER BY
to get these consistent results. Sorry, but SQL is *defined* to
not produce consistent row ordering without ORDER BY. Changing
that isn't a matter of some optimizer hint somewhere, it's a very
fundamental thing in many places.
I'd counsel taking another look at the suggestion made upthread
to use a cursor WITH HOLD.
regards, tom lane
view thread (4+ messages) latest in thread
Message-ID: <323666.1593441019@sss.pgh.pa.us>
Permalink: ../323666.1593441019@sss.pgh.pa.us/
Also on: postgresql.org/message-id/323666.1593441019@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, martin.handsteiner@sibvisions.com, simon@2ndquadrant.com, pgsql-sql@lists.postgresql.org
Subject: Re: AW: Optimizer Hint, to ignore limit and offset in optimizer plan
In-Reply-To: <323666.1593441019@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox