Agreed on the 2 indices. I only added the second non-unique index to test the hypothesis that i was doing an order-by col1, col2 when the original unique index was on col1, col2, col3...
Also, the original statement i implemented did not have all of that. This is the normalized SQL that Postgres now gives when looking at the indices. Collation for the DB is "en_US.UTF-8" and that's used for the defaults i suspect?
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, January 25, 2019 1:10:55 PM
To: ldh@laurent-hasson.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Zero throughput on a query on a very large table.
"ldh@laurent-hasson.com" <ldh@laurent-hasson.com> writes:
> The indices are defined as:
> CREATE INDEX i_outprev_ptclaim
> ON public.tmp_outpatient_rev USING btree
> (desy_sort_key COLLATE pg_catalog."default", claim_no COLLATE pg_catalog."default")
> TABLESPACE pg_default;
> CREATE UNIQUE INDEX ui_outprev_ptclaimline
> ON public.tmp_outpatient_rev USING btree
> (desy_sort_key COLLATE pg_catalog."default", claim_no COLLATE pg_catalog."default", clm_line_num COLLATE pg_catalog."default")
> TABLESPACE pg_default;
I'm a bit suspicious of those explicit COLLATE clauses; seems like maybe
they could be accounting for not matching to the query-requested order.
Perhaps they're different from the collations specified on the underlying
table columns?
Also, it seems unlikely that it's worth the maintenance work to keep
both of these indexes, though that's not related to your immediate
problem.
regards, tom lane