Re: Zero throughput on a query on a very large table.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Zero throughput on a query on a very large table.
Date: 2019-01-25 18:10:55
Message-ID: 2201.1548439855@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message ldh@laurent-hasson.com 2019-01-25 18:20:53 Re: Zero throughput on a query on a very large table.
Previous Message ldh@laurent-hasson.com 2019-01-25 18:00:31 Re: Zero throughput on a query on a very large table.