Indexes work very well to only validate relevant sections in PostgreSQL. But you need to configure everything correctly so that it works, and it’s easy to skip a step in the long list of things documented at http://www.postgresql.org/docs/current/static/ddl-partitioning.html
The main thing to understand is that in order to avoid sequential scans, you must provide sufficient PostgreSQL information so that it can prove that some sections may not have the data you are looking for; then they are skipped as potential sources for query results. The article you are referencing refers to as a solution to the seq scan problem: "If you add range limits to the date field of each section, this query can be optimized in a loop where you first request the" last "section and work back until you find one value that exceeds the range of all other sections. " - but does not show the improved plan that you will see after this change.
Some common mistakes you could make:
- The constraint_exclusion parameter in the postgresql.conf file is disabled by default. With this default value, you will not get what you expect.
-Do not create non-overlapping partitions using CHECK, which allows the scheduler to find out what is inside each of them. It is possible to skip this step, but all the same, correctly your data falls into the correct sections, the scheduler just does not know this.
- Do not put the index in each section only created in the main table. This will give you sequential scanning only on the corresponding section, so it is not as bad as the above, but not good.
There are some works to make this easier in upcoming PostgreSQL releases (setting constraint_partition is pretty automated in 8.4 and partitions are automated to some extent). Right now, if you carefully follow the instructions and avoid all these problems, it should work.