Why is PostgreSQL selecting a slow Seq scan when an index scan would be better?

I have the following tables in a Postgres 9.6 DB:

create table baseDimensions(
id serial not null primary key,
dimension1 date not null,
dimension2 int not null,
dimension3 text not null,
-- ...
dimension10 boolean not null,
unique(dimension1, dimension2, ..., dimension10)
);

create table interestingData(
baseDimensionId int not null references baseDimensions(id),
subdimension1 int not null,
subdimension2 boolean not null,
-- ...
value1 int not null,
value2 bigint not null,
-- ...
primary key(baseDimensionId, subdimension1, subdimension2)
)


So in text: I have got lots of dimensions (e.g. if the tables would be for the sales of a retail store the dimensions could be date of sale, customerId, whether the customer is a repeated customer, the color of the jacket the customer was wearing etc. etc) and some values for those dimensions (e.g. to stay with the retail example: A value could be the amount the customer paid). There are multiple "interestingData" tables that all however share the ten base dimensions, so to save some disk space I extracted those base dimensions into a separate table.

I purposefully selected the order of columns in the unique index so that typical filter conditions are left-most. So most of the time I am going to filter data by dimension1.

Now I would like to know the average value1 on a specific day (dimension1 for each dimension3). This can be answered with the following query:

select dimension3, avg(value1)
from baseDimensions b
join interestingData c on b.id = c.baseDimensionId
where b.dimension1 = '2016-08-20'::date
group by dimension3


The explain+analyze for this query looks like this:

```
HashAggregate (cost=1141685.72..1141686.14 rows=28 width=41) (actual time=55824.222..55827.068 rows=3358 loops=1)
Group Key: b.dimension3
-> Hash Join (cost=63915.68..1139740.91 rows=259308 width=41) (actual time=9956.393..55684.492 rows=267004 loops=1)
Hash Cond

It looks like all the data you need to resolve this query via the nested loop was already cached in RAM. PostgreSQL's planner would not be aware of that, it assumes it is going to have to go to disk to get much of the data.

If it is all cached, that could happen either fairly or unfairly.

Fairly could be that you have a lot of RAM and most of your data is in cache most of the time. In that case, lowering random_page_cost to be the same or nearly the same as seq_page_cost is the correct response. But be warned that if you ever restart the server, you could be in for a painful warm up period as the cache gets reloaded from disk.

Another "fairly in cache" would be that you actually run the exact same query with the exact same parameter (2016-08-20) very often in production, and so that particular set of data stays in memory even though most of your data does not. In this case, lowering random_page_cost could fix this particularly query, but make other ones worse. One solution would be to lower random_page_cost or set enable_seqscan=off just for this query and reset it afterwards, if your framework lets you do that.

"Unfairly in cache" would be that you keep testing this query with a parameter of 2016-08-20, when your real query will use a different parameter each time. This means your performance testing server gets to re-use the same data over and over without reading it from disk, while your production server would not be able to do so. In this case you need to improve your testing/benchmarking method to be more realistic.

StackExchange Database Administrators Q#161874, answer score: 5