Killing performance with PostgreSQL partitioning

Partitioning Alone Won’t Save a Full-Table Scan

Choosing the right partitioning strategy is entirely about the workload. In a table holding 2 billion rows, for instance, heavy querying on the user_id column makes hash partitioning a strong candidate, since that lets the database narrow its scan to the relevant partition when user_id appears in the WHERE clause.

But the query in question used SELECT * with no WHERE clause at all. That forces the database to scan every row across every partition, blunting the benefits of any partitioning scheme and leaving the query slow regardless of strategy.