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

Since the PGADmin4 client timed out when creating the index, you picked my interest here and i was wondering if the index creation itself had failed... but:

\d tmp_outpatient_rev

Indexes:
"ui_outprev_ptclaimline" UNIQUE, btree (desy_sort_key, claim_no, clm_line_num)
"i_outprev_ptclaim" btree (desy_sort_key, claim_no)

So looks like the indices are file. I am pursuing some of the other recommendations you suggested before.

Thank you,

Laurent.

________________________________
From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Sent: Friday, January 25, 2019 1:55:31 AM
To: Tom Lane
Cc: ldh(at)laurent-hasson(dot)com; pgsql-performance(at)postgresql(dot)org
Subject: Re: Zero throughput on a query on a very large table.

On Fri, 25 Jan 2019 at 19:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> PS: On the third hand, you mention having created new indexes on this
> table with apparently not a lot of pain, which is a tad surprising
> if you don't have the patience to wait for a sort to finish. How
> long did those index builds take?

It would certainly be good to look at psql's \d tmp_outpatient_rev
output to ensure that the index is not marked as INVALID.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services