Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

On 23 February 2018 at 04:00, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> 2) The negative impact of an unused index is tiny compared to the negative
> impact of a missing index.

I'd say that would entirely depend on the workload of the table and
the entire cluster. Disk space and I/O to write WAL and index pages to
is surely a finite resource. Not to mention the possibility of
disallowing HOT-Updates in the heap.

It would seem to me that anyone using the "shotgun" indexing method
may end up having to learn more about indexing the hard way. Learning
the hard way is something I like to try to avoid, personally. Probably
it all has to come down to how important it is that your app actually
can handle the load vs devs/dba experience level divided by time, both
of the latter two are also a finite resource. So, it probably all has
to be carefully balanced and quite possibly a person's opinion
strongly relates to their experience. If you were in the air traffic
control business, perhaps your opinion might not be the same!? ...
Sorry, the aeroplane crashed because the replica instance lagged and
the plane's location wasn't updated... Analysis shows that the DBA
indexed every column in the table and the WAL volume was more than the
network's bandwidth could handle over the holiday period. (Note: I
know nothing about air traffic control, but it does seem like
something you'd want to make stable systems for, games on the
internet, probably less so...).

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