From: Tom Lane <tgl@sss.pgh.pa.us>
To: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Zero throughput on a query on a very large table.
Date: Fri, 25 Jan 2019 13:10:55 -0500
Message-ID: <2201.1548439855@sss.pgh.pa.us> (raw)
In-Reply-To: <BN6PR15MB11858B89B18A47FAB8430A34859B0@BN6PR15MB1185.namprd15.prod.outlook.com>
References: <BN6PR15MB1185DB9366CD57353DDD22C4859B0@BN6PR15MB1185.namprd15.prod.outlook.com>
<29285.1548397485@sss.pgh.pa.us>
<BN6PR15MB11858B89B18A47FAB8430A34859B0@BN6PR15MB1185.namprd15.prod.outlook.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ldh@laurent-hasson.com
Subject: Re: Zero throughput on a query on a very large table.
In-Reply-To: <2201.1548439855@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox