Re: Zero throughput on a query on a very large table. - Mailing list pgsql-performance

From Tom Lane
Subject Re: Zero throughput on a query on a very large table.
Date
Msg-id 2201.1548439855@sss.pgh.pa.us
Whole thread Raw
In response to Re: Zero throughput on a query on a very large table.  ("ldh@laurent-hasson.com" <ldh@laurent-hasson.com>)
Responses Re: Zero throughput on a query on a very large table.  ("ldh@laurent-hasson.com" <ldh@laurent-hasson.com>)
List pgsql-performance
"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


pgsql-performance by date:

Previous
From: "ldh@laurent-hasson.com"
Date:
Subject: Re: Zero throughput on a query on a very large table.
Next
From: "ldh@laurent-hasson.com"
Date:
Subject: Re: Zero throughput on a query on a very large table.