Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gn5wU-0003Zo-MD for pgsql-performance@arkaria.postgresql.org; Fri, 25 Jan 2019 18:11:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gn5wT-0005XC-5o for pgsql-performance@arkaria.postgresql.org; Fri, 25 Jan 2019 18:11:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gn5wS-0005X2-Ui for pgsql-performance@lists.postgresql.org; Fri, 25 Jan 2019 18:11:00 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gn5wQ-000577-LY for pgsql-performance@postgresql.org; Fri, 25 Jan 2019 18:11:00 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id x0PIAts2002202; Fri, 25 Jan 2019 13:10:55 -0500 From: Tom Lane To: "ldh@laurent-hasson.com" cc: "pgsql-performance@postgresql.org" Subject: Re: Zero throughput on a query on a very large table. In-reply-to: References: ,<29285.1548397485@sss.pgh.pa.us> Comments: In-reply-to "ldh@laurent-hasson.com" message dated "Fri, 25 Jan 2019 18:00:31 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2200.1548439855.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 25 Jan 2019 13:10:55 -0500 Message-ID: <2201.1548439855@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk "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_cat= alog."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_cat= alog."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