Horton

Re: Faster distinct query?

Mladen Gogala <gogala.mladen@gmail.com>

View thread View on postgr.es
From: Mladen Gogala <gogala.mladen@gmail.com>
To: pgsql-general@lists.postgresql.org
Date: 2021-09-23T03:02:12Z
Lists: pgsql-general 
On 9/22/21 16:20, David G. Johnston wrote:
> I'd probably turn that index into a foreign key that just ensures that 
> every (station,channel) that appears in the data table also appears on 
> the lookup table.  Grouping and array-ifying the lookup table would be 
> trivial.  Either modify the application code or add a trigger to 
> populate the lookup table as needed.


I fully agree with this. Adding a trigger to populate a lookup table is 
a standard design in situations like this. Using "DISTINCT" almost 
always spells trouble for the performance.


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com