Re: BUG #14780: PostgreSQL 9.6 selects a wrong plan during aggregation against timestamp columns

On 16 August 2017 at 02:47, <sogawa(at)yandex(dot)ru> wrote:
>
> select min(day) from log where user_id =
> 'ab056f5a-390b-41d7-ba56-897c14b679bf'

This is a classic database optimization problem that is difficult to
always get right. Whether to use an index on day to find the lowest
values and scan until you find the specified user_id or to scan all
the records for that user_id to find the minimum day will depend on
the number of records each user_id has and how they're distributed
across the days. If you have some users with many records then using
an index on user_id can perform terribly when those ids are
referenced.

The classic solution is to have an index on <user_id, day> and then
the database can look up the correct value in a single index probe.

These kinds of problems are better addressed to pgsql-general

--
greg