Re: inconsistent results querying table partitioned by date - Mailing list pgsql-bugs

On 2019/05/14 10:50, David Rowley wrote:
> On Mon, 13 May 2019 at 17:40, Amit Langote wrote:
>> On 2019/05/11 6:05, Tom Lane wrote:
>>> regression=# explain select * from dataid where id=1 and datadatetime < '2018-01-01'::timestamptz;
>>>                                                QUERY PLAN
>>> --------------------------------------------------------------------------------------------------------
>>>  Bitmap Heap Scan on dataid_default  (cost=4.18..11.30 rows=3 width=12)
>>>    Recheck Cond: ((id = 1) AND (datadatetime < '2018-01-01 00:00:00-05'::timestamp with time zone))
>>>    ->  Bitmap Index Scan on dataid_default_pkey  (cost=0.00..4.18 rows=3 width=0)
>>>          Index Cond: ((id = 1) AND (datadatetime < '2018-01-01 00:00:00-05'::timestamp with time zone))
>>> (4 rows)
>>>
>>> That's not fine.  What we have here is a "timestamp < timestamptz"
>>> operator, which is only stable, therefore it might give different
>>> results at runtime than at plan time.  You can't make plan-time
>>> pruning decisions with that.  What we should have gotten here was
>>> an Append node that could do run-time pruning.
>>
>> You're right.  It seems that prune_append_rel_partitions() is forgetting
>> to filter mutable clauses from rel->baserestrictinfo, like
>> relation_excluded_by_constraints() does.  I fixed that in the attached
>> 0003 patch, which also adds a test for this scenario.  I needed to also
>> tweak run-time pruning support code a bit so that it considers the cases
>> involving mutable functions as requiring (startup) run-time pruning, in
>> addition to the cases with mutable expressions.  Adding David if he wants
>> to comment.
> 
> Yeah. I don't think you're going about this the right way.  I don't
> really see why we need to make any changes to the run-time pruning
> code here, that part seems fine to me.  The problem seems to be that
> match_clause_to_partition_key() thinks it can use a non-const
> expression to compare to the partition key.  All immutable function
> calls will already be folded to constants by this time, so what's
> wrong with just insisting that the value being compared to the
> partition key is a constant when generating steps during planning?

The problem is different.  '2018-01-01'::timestamptz' in the condition
datadatetime < '2018-01-01'::timestamptz as presented to
match_clause_to_partition_key() is indeed a Const node, making it think
that it's OK to prune using it, that is, with or without your patch.
Here's the result for Tom's query quoted above, with your patch applied:

explain analyze select * from dataid where id=1 and datadatetime <
'2018-01-01'::timestamptz;
                                                         QUERY PLAN


────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan on dataid_default  (cost=4.18..11.30 rows=3 width=12)
(actual time=0.050..0.058 rows=0 loops=1)
   Recheck Cond: ((id = 1) AND (datadatetime < '2018-01-01
00:00:00+09'::timestamp with time zone))
   ->  Bitmap Index Scan on dataid_default_pkey  (cost=0.00..4.18 rows=3
width=0) (actual time=0.027..0.035 rows=0 loops=1)
         Index Cond: ((id = 1) AND (datadatetime < '2018-01-01
00:00:00+09'::timestamp with time zone))
 Planning Time: 33660.807 ms
 Execution Time: 0.236 ms
(6 rows)

which is same as without the patch and is wrong as Tom complains.  His
complaint is that planning-time pruning should not have considered this
clause, because its result is only stable, not immutable.  That is, the
operator '<' (function timestamp_lt_timestamptz() in this case) is only
stable, not immutable.  The expected plan in this case is Append node with
run-time pruning set up and initial pruning will do the pruning, which you
get with my patch:

explain select * from dataid where id=1 and datadatetime <
'2018-01-01'::timestamptz;
                                                  QUERY PLAN

──────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Append  (cost=4.18..22.63 rows=6 width=12)
   Subplans Removed: 1
   ->  Bitmap Heap Scan on dataid_default  (cost=4.18..11.30 rows=3 width=12)
         Recheck Cond: ((id = 1) AND (datadatetime < '2018-01-01
00:00:00+09'::timestamp with time zone))
         ->  Bitmap Index Scan on dataid_default_pkey  (cost=0.00..4.18
rows=3 width=0)
               Index Cond: ((id = 1) AND (datadatetime < '2018-01-01
00:00:00+09'::timestamp with time zone))
(6 rows)

The case you seem to be thinking of is where the condition is of shape
"partkey op stable-valued-function", but that case works fine today, so
needs no fixing.

Thanks,
Amit