From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | dmigowski(at)ikoffice(dot)de |
Subject: | BUG #18206: Strange performance behaviour depending on location of field in query. |
Date: | 2023-11-19 15:33:16 |
Message-ID: | 18206-5a796f4945e5b39f@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18206
Logged by: Daniel Migowski
Email address: dmigowski(at)ikoffice(dot)de
PostgreSQL version: 15.5
Operating system: Windows + Linux
Description:
I have a table with some columns id and a to z and these two queries have
drastically different runtimes.
select count(id) FROM testtable t WHERE t.z IS NULL; (35% slower than
WHERE t.a IS NULL)
select count(id) FROM testtable t WHERE t.a IS NULL;
This just screems like somewhere PostgreSQL is iterating over fields over
and over again without assigning field indexes to the literals.
Please create a test table this way:
drop table if exists testtable cascade;
SELECT generate_series::int4 as id, null::int4 a, null::int4 b,
null::int4 c, null::int4 d, null::int4 e, null::int4 f, null::int4 g,
null::int4 h, null::int4 i, null::int4 j,
null::int4 k, null::int4 l, null::int4 m, null::int4 n, null::int4 o,
null::int4 p, null::int4 q, null::int4 r,
null::int4 s, null::int4 t, null::int4 u, null::int4 v, null::int4 w,
null::int4 x, null::int4 y, null::int4 z
into testtable
FROM generate_series(1,6000000,1);
Doesn't matters if parallel query is used, can also be deactivated. But it's
strange to see that reordering the physical column layout can have such a
large effect on the tables.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-11-19 16:58:42 | Re: BUG #18206: Strange performance behaviour depending on location of field in query. |
Previous Message | PG Bug reporting form | 2023-11-19 15:26:32 | BUG #18205: Performance regression with NOT NULL checks. |