Re: R: R: Slow queries on very big (and partitioned) table

Greetings,

* Job (Job(at)colliniconsulting(dot)it) wrote:
> >If this is really what you're mostly doing, having constraint exclusion and an index on 'profile' would probably be enough, if you insist on continuing to have the table partitioned by day (which I continue to argue is a bad idea-
> >based on the number of total rows you mentioned and the number of partitions, you have partitions with less than 20M rows each and that's really small, month-based partitions with a BRIN would probably work better). If you get to
> >the point of having years worth of daily partitions, you'd going to see increases in planning time.
>
> Based on our simulation, we can raise until 5Gb of datas for a single day, with some millions of rows.

5GB and a few millions rows isn't actually all that much.

> We thought to implement one partition for day.
> Do you think it should be fine?

Really depends on what you're doing. If you're running very short
queries that pull out just a record or a few records, then you're going
to be unhappy with the planning time required when you have hundreds and
thousands of partitions, which is why I typically recommend against
using partitions-by-day unless you're only keeping a few months worth of
data.

Thanks!

Stephen