From: Andreas Kretschmer <andreas@a-kretschmer.de>
To: pgsql-performance@lists.postgresql.org
Subject: Re: postgresql 10.1 wrong plan in when using partitions bug
Date: Sun, 4 Feb 2018 15:54:40 +0100
Message-ID: <fe3a1929-c843-e10a-8580-302c60db4c13@a-kretschmer.de> (raw)
In-Reply-To: <CA+t6e1mt-fkvZZX5Wx8B1oPrw=DQOZGr3s=xU_ZhEhKEJtJ-Og@mail.gmail.com>
References: <CA+t6e1m54jrEj7spf9ArH5BJiO-Qznn=_YCRGWWMtohvXg7mEg@mail.gmail.com>
<f042e9cb-be4b-4215-4165-8a14313cbdf7@2ndquadrant.com>
<CA+t6e1mt-fkvZZX5Wx8B1oPrw=DQOZGr3s=xU_ZhEhKEJtJ-Og@mail.gmail.com>
Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
> I checked the plan of the next query :
> explain select count(*) from log_full where end_date between
> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
>
can you rewrite the query to
... where end_date between '2017/12/03' and '2017/12/03'
simple test-case:
test=*# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+------+-----------+----------+---------+---------+--------------+-------------
d | date | | | | plain | |
Partition key: RANGE (d)
Partitions: t_01 FOR VALUES FROM ('2018-02-04') TO ('2018-02-05'),
t_02 FOR VALUES FROM ('2018-02-05') TO ('2018-02-06')
test=*# explain analyse select * from t where d between
to_date('2018/02/04','YY/MM/DD') and to_date('2018/02/04','YY/MM/DD');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..122.00 rows=26 width=4) (actual time=0.006..0.006
rows=0 loops=1)
-> Seq Scan on t_01 (cost=0.00..61.00 rows=13 width=4) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text))
AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
-> Seq Scan on t_02 (cost=0.00..61.00 rows=13 width=4) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text))
AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
Planning time: 0.241 ms
Execution time: 0.042 ms
(7 rows)
test=*# explain analyse select * from t where d between '2018/02/04' and
'2018/02/04';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Append (cost=0.00..48.25 rows=13 width=4) (actual time=0.005..0.005
rows=0 loops=1)
-> Seq Scan on t_01 (cost=0.00..48.25 rows=13 width=4) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((d >= '2018-02-04'::date) AND (d <= '2018-02-04'::date))
Planning time: 0.203 ms
Execution time: 0.030 ms
(5 rows)
test=*#
maybe the planner should be smart enough to do that for you, but
obvously he can't. So it's a workaround, but it seems to solve the problem.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@a-kretschmer.de, pgsql-performance@lists.postgresql.org
Subject: Re: postgresql 10.1 wrong plan in when using partitions bug
In-Reply-To: <fe3a1929-c843-e10a-8580-302c60db4c13@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox