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