PostgreSQL 9.6：按时间戳日期分区表查询全扫及分区异常问题

Let's break down your problem and fix that annoying full partition scan issue.

First, the root cause: When you use s_date::date in your WHERE clause, PostgreSQL's query planner can't automatically map that date condition back to the timestamp range your partitions are based on. Since your table is partitioned on the s_date timestamp column, the planner needs explicit timestamp range comparisons to know which partitions to skip—type conversions like ::date act as a black box here, so it plays it safe and scans all partitions.

Here are the concrete fixes you can apply: #

1. Rewrite Your Query to Use Timestamp Ranges (Best Solution)

Instead of converting the timestamp to a date, directly specify the start and end of the date's timestamp range. This lets the planner immediately match your condition to the partition boundaries:

SELECT * 
FROM diagnoseAW.AWIORECORDERAWCOMMAND
WHERE s_date >= '2024-01-01'::timestamp  -- Start of the target date
  AND s_date < '2024-01-02'::timestamp; -- Start of the next day (exclusive)

This is the most reliable way to trigger partition pruning in PostgreSQL 9.6.

2. Verify Partition Pruning is Enabled

Double-check that the database is allowed to prune partitions. Run this query to confirm:

SHOW enable_partition_pruning;

If the result is off, enable it with:

SET enable_partition_pruning = on;

Note: In PostgreSQL 9.6, this parameter is enabled by default, but it's worth confirming in case it was overridden.

3. Avoid Function Calls on the Partition Key

Any time you apply a function (including type casts like ::date or date_trunc()) to your partition key (s_date), you're making it harder for the planner to do its job. Stick to direct comparisons with the partition key's native type (timestamp) whenever possible.

Why This Works for Your "Working" Partition Table #

Your first table works as expected because you're likely querying it with direct timestamp conditions (not using ::date), so the planner can easily match the query to the partition ranges. The second table's issue is purely about how you're writing the WHERE clause, not the partition setup itself.

内容的提问来源于stack exchange，提问作者Artemur of Artemur