Re: Faster distinct query?

> On Sep 22, 2021, at 12:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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.

I do have autovaccum turned on, but perhaps I need to do a manual? The initial population of the database was accomplished via logical replication from a different database cluster (needed to move this database to more dedicated hardware), so perhaps that left the database in a state that autovaccum doesn’t address? Or perhaps my autovaccum settings aren’t kosher - I haven’t adjusted that portion of the config any.

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145

>
> regards, tom lane