pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop
6+ messages / 4 participants
[nested] [flat]
* BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop
@ 2010-01-20 14:23 Richard Neill <rn214@cam.ac.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Neill @ 2010-01-20 14:23 UTC (permalink / raw)
To: pgsql-bugs
The following bug has been logged online:
Bug reference: 5293
Logged by: Richard Neill
Email address: rn214@cam.ac.uk
PostgreSQL version: 8.4.2
Operating system: Linux
Description: constant function (date_trunc) is repeatedly evaluated
inside loop
Details:
SUMMARY
-------
If I have a WHERE clause such as this:
WHERE srep_timestamp >= date_trunc('day', timestamp '2010-01-20 10:16:55')
...
then I'd expect the query planner to evaluate the constant function
date_trunc('day', timestamp '2010-01-20 10:16:55')
once, outside the loop.
However, it doesn't do this.
As a result, the query time doubles from 160ms to 340ms
compared to:
WHERE srep_timestamp >= '2010-01-20 00:00:00') ...
DETAILS
-------
Here are some actual results from a 250k row table.
srep_timestamp has times roughly linearly distributed over a 2 day period
(with about 20% nulls).
There is an index tbl_tracker_srepz_timestamp_idx on srep_timestamp WHERE
srep_timestamp is not null.
The measured times are consistent and repeatable.
SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= '2010-01-20
00:00:00') AND (srep_timestamp < '2010-01-21 00:00:00') );
count
--------
198577
(1 row)
Time: 158.084 ms
SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= date_trunc('day',
timestamp '2010-01-20 10:16:55') AND srep_timestamp < date_trunc('day',
timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' )) ;
count
--------
198577
(1 row)
Time: 341.155 ms
explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
'2010-01-20 00:00:00') AND (srep_timestamp < '2010-01-21 00:00:00') );
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------
Aggregate (cost=3181.17..3181.18 rows=1 width=0) (actual
time=663.651..663.652 rows=1 loops=1)
-> Bitmap Heap Scan on tbl_tracker (cost=29.39..3177.97 rows=1279
width=0) (actual time=101.197..396.428 rows=198577 loops=1)
Recheck Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
-> Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx
(cost=0.00..29.07 rows=1279 width=0) (actual time=98.417..98.417 rows=198577
loops=1)
Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
Total runtime: 663.769 ms
(6 rows)
Time: 665.087 ms
explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
date_trunc('day', timestamp '2010-01-20 10:16:55') AND srep_timestamp <
date_trunc('day', timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' ))
;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------
Aggregate (cost=3181.17..3181.18 rows=1 width=0) (actual
time=827.424..827.425 rows=1 loops=1)
-> Bitmap Heap Scan on tbl_tracker (cost=29.39..3177.97 rows=1279
width=0) (actual time=276.367..563.503 rows=198577 loops=1)
Recheck Cond: ((srep_timestamp >= '2010-01-20 00:00:00'::timestamp
without time zone) AND (srep_timestamp < '2010-01-21 00:00:00'::timestamp
without time zone))
-> Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx
(cost=0.00..29.07 rows=1279 width=0) (actual time=275.020..275.020
rows=198577 loops=1)
Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00'::timestamp without time zone) AND (srep_timestamp < '2010-01-21
00:00:00'::timestamp without time zone))
Total runtime: 827.534 ms
(6 rows)
Time: 828.763 ms
Thanks very much - Richard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop
@ 2010-01-20 15:19 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Richard Neill <rn214@cam.ac.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-01-20 15:19 UTC (permalink / raw)
To: Richard Neill <rn214@cam.ac.uk>; pgsql-bugs
"Richard Neill" <rn214@cam.ac.uk> wrote:
> date_trunc('day', timestamp '2010-01-20 10:16:55')
What happens with a "timestamp with time zone" literal?
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop
@ 2010-01-20 16:56 Richard Neill <rn214@cam.ac.uk>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Neill @ 2010-01-20 16:56 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-bugs
Kevin Grittner wrote:
> "Richard Neill" <rn214@cam.ac.uk> wrote:
>
>> date_trunc('day', timestamp '2010-01-20 10:16:55')
>
> What happens with a "timestamp with time zone" literal?
>
> -Kevin
>
Good call!
This query is fast:
SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
date_trunc('day', timestamp with time zone '2010-01-20 10:16:55') AND
srep_timestamp < date_trunc('day', timestamp with time zone '2010-01-20
10:16:55') + INTERVAL '24 hour' )) ;
In other words:
#fast
WHERE column < '2010-010-20 00:00:00'
#fast
WHERE column < date_trunc('day', timestamp with time zone
'2010-01-20 10:16:55')
#slow
WHERE column < date_trunc('day', timestamp
'2010-01-20 10:16:55')
Why is that, I wonder?
Richard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop
@ 2010-01-20 17:04 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Richard Neill <rn214@cam.ac.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2010-01-20 17:04 UTC (permalink / raw)
To: Richard Neill <rn214@cam.ac.uk>; +Cc: pgsql-bugs
Richard Neill <rn214@cam.ac.uk> wrote:
> #fast
> WHERE column < '2010-010-20 00:00:00'
>
> #fast
> WHERE column < date_trunc('day', timestamp with time zone
> '2010-01-20 10:16:55')
>
> #slow
> WHERE column < date_trunc('day', timestamp
> '2010-01-20 10:16:55')
>
>
> Why is that, I wonder?
The column is timestamp with time zone, and the fast options
generate a value of the same type (the first because the literal is
treated as UNKNOWN type until the comparison). It's generally a
good idea to use literals which match the type of the column. How
much work it would be to optimize the slow case to insert a cast of
the date_trunc function return value to a different type before
entering the loop where the value is tested, I don't know offhand.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* XML column causes record to be both NULL and NOT NULL
@ 2023-07-04 18:46 Wilm Hoyer <W.Hoyer@dental-vision.de>
0 siblings, 1 reply; 6+ messages in thread
From: Wilm Hoyer @ 2023-07-04 18:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
i found a strange behavior and hope it's not a pilot error.
PostgreSQL Version:
PostgreSQL 14.8, compiled by Visual C++ build 1914, 64-bit
Reproducer:
CREATE TABLE IF NOT EXISTS public.test
(
num integer NOT NULL,
prev integer
);
INSERT INTO public.test
VALUES (1, null),
(2, 1),
(3, 2);
CREATE OR REPLACE FUNCTION public.steps()
RETURNS SETOF test
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
nextVal public.test;
BEGIN
SELECT * INTO nextVal FROM public.test
WHERE prev = 2;
RAISE NOTICE 'nextVal IS NULL? % or nextVal IS NOT NULL? %', nextVal IS NULL, nextVal IS NOT NULL;
IF nextVal IS NOT NULL THEN
RETURN NEXT nextVal;
END IF;
END;
$BODY$;
SELECT * FROM steps();
-- returns: 3, 2
-- NOTICE: nextVal IS NULL? f or nextVal IS NOT NULL? t
ALTER TABLE public.test ADD COLUMN x xml;
SELECT * FROM steps();
-- returns: nothing ; expected 3, 2
-- NOTICE: nextVal IS NULL? f or nextVal IS NOT NULL? f
I reduced this from a rather complicate function until I stumbled upon the freshly added xml column causing the wrong results.
Sure it could be further condensed, but I hope it's small enough for a report now.
Best wishes and keep up the great work
Wilm Hoyer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: XML column causes record to be both NULL and NOT NULL
@ 2023-07-04 18:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wilm Hoyer <W.Hoyer@dental-vision.de>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2023-07-04 18:59 UTC (permalink / raw)
To: Wilm Hoyer <W.Hoyer@dental-vision.de>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Wilm Hoyer <W.Hoyer@dental-vision.de> writes:
> i found a strange behavior and hope it's not a pilot error.
Well, it is strange, but it's acting per SQL spec.
As stated at [1]:
If the expression is row-valued, then IS NULL is true when the row
expression itself is null or when all the row's fields are null, while
IS NOT NULL is true when the row expression itself is non-null and all
the row's fields are non-null. Because of this behavior, IS NULL and
IS NOT NULL do not always return inverse results for row-valued
expressions; in particular, a row-valued expression that contains both
null and non-null fields will return false for both tests.
The fact that the added column is of XML type isn't relevant here;
what's relevant is that it has a null value.
regards, tom lane
[1] https://www.postgresql.org/docs/current/functions-comparison.html
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-07-04 18:59 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-20 14:23 BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop Richard Neill <rn214@cam.ac.uk>
2010-01-20 15:19 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-01-20 16:56 ` Re: BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop Richard Neill <rn214@cam.ac.uk>
2010-01-20 17:04 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2023-07-04 18:46 XML column causes record to be both NULL and NOT NULL Wilm Hoyer <W.Hoyer@dental-vision.de>
2023-07-04 18:59 ` Re: XML column causes record to be both NULL and NOT NULL Tom Lane <tgl@sss.pgh.pa.us>
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