Re: select distinct runs slow on pg 10.6

On Wed, Sep 11, 2019 at 12:57 PM Rick Otten <rottenwindfish(at)gmail(dot)com> wrote:
>
> On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <dinesh(at)opsveda(dot)com> wrote:
>>
>> I think Merlin has outlined pretty much all the options and very neatly. (As an asides Merlin could you possibly elaborate on the "C Hack" how that might be accomplished.)
>>
>> To OP, I am curious if the performance changes were the query rewritten such that all timestamp columns were listed first in the selection. I understand it might not be feasible to make this change in your real application without breaking the contract.
>>
>> Regards
>> Dinesh
>
>
> It looks like AWS has a pgbouncer query re-writer service that might be a starting point:
> https://aws.amazon.com/blogs/big-data/query-routing-and-rewrite-introducing-pgbouncer-rr-for-amazon-redshift-and-postgresql/
>
> I've never used it.

Yeah, I haven't either. Side note: this system also provides the
ability to load balance queries across distributed system; that's a
huge benefit. Say you have master server and five replica, it seems
that you can round robin the read only queries using this system or
other neat little tricks. I would be cautious about pgbouncer-rr
becoming the bottleneck itself for certain workloads though.

Anyways, a 'hack' strategy on linux might be to:
*) Check and verify that libpq is dynamically linked (which is almost
alwasys the case). ldd /your/application should give the dynamic
library dependency to libpq.
*) Grab postgres sources for same version as production
*) configure
*) switch to interfaces/libpq
*) figure out which interface routine(s) being called into. The
approach will be slightly different if the query is
prepared/paramterized or not. Assuming it isn't, you'd have to modify
the PQsendQuery routine to check for the signature (say, with
strcmp), create a new string, and have that be put instead of the
incoming const char* query. The parameterized versions
(PQsendQueryParams) would be easier since you'd be able to use a
static string rather than parsing it out.
*) Build the library, do some testing with hand written C program
*) inject the modified libpq with LD_LIBRARY_PATH

It must be stated that some people might read this and be compelled to
barf :-) -- it's pretty gross. Having said that, sometimes you have to
find a solution. I would definitely try the pgbouncer-rr approach
first however; this has a *lot* of potential benefit.

merlin