Horton

Re: Faster distinct query?

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>, Israel Brewster <ijbrewster@alaska.edu>, PostgreSQL Mailing Lists <pgsql-general@postgresql.org>
Date: 2021-09-22T20:48:42Z
Lists: pgsql-general 
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> There is no where clause so I'm doubtful there is much to be gained going
> down this path.  The Index-Only scan seems like an optimal way to obtain
> this data and the existing query already does that.

The "index-only" scan is reported to do 86m heap fetches along the
way to returning 812m rows, so the data is apparently pretty dirty.
It's possible that a preliminary VACUUM to get page-all-visible hint
bits set would be a net win.

			regards, tom lane