pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feednew BUG: "postgresql 9.2.3: very long query time"
8+ messages / 4 participants
[nested] [flat]
* new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-20 13:42 Claude Speed <gta3user@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Claude Speed @ 2013-02-20 13:42 UTC (permalink / raw)
To: pgsql-bugs
Hi all,
Postgresql 9.2.3 is processing my query is much longer than Postgresql
9.1.8:
Postgresql 9.1.8 - *2292* ms
Postgresql 9.2.3 - *163336* ms
I provided my query in attach and the database dump too,
this bug is reproducible.
Operating system: Gentoo x86_64 Linux 2.6.36
select sle."shiftDate" as shift_date,
cast (
sum (
sle."productCount" * template1."lengthBlankSize" * template1."widthBlankSize" / 1000000 * case when template2."id" = product2."id" then bspg."numbersProduct" else 1 end
) as int8
) as square_meters
from SensorLogEntry sle inner join Job job on sle."job" = job."id"
inner join OrderStep os on job."orderStep" = os."id"
inner join cb_order order1 on os."order_id" = order1."id"
inner join Template template2 on order1."template" = template2."id"
left outer join Product product1 on template2."id" = product1."id",
Template template1 cross join Product product2 inner join Template template3 on product2."id" = template3."id",
TechnologyCard tc,
BlankSizeParameterGroup bspg
where order1."template" = template1."id"
and product2."id" = tc."product"
and tc."bl_size_fk" = bspg."id"
and (
template2."id"=product2."id"
or case when product1."id" is not null then 1
when template2."id" is not null then 0
end <>1
and product2."id"=(select max(product3."id") from Product product3 inner join Template template4 on product3."id"=template4."id")
)
group by sle."shiftDate"
order by sle."shiftDate";
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Attachments:
[text/plain] query.sql (1.2K, ../../5124D2BE.5090001@gmail.com/3-query.sql)
download | inline:
select sle."shiftDate" as shift_date,
cast (
sum (
sle."productCount" * template1."lengthBlankSize" * template1."widthBlankSize" / 1000000 * case when template2."id" = product2."id" then bspg."numbersProduct" else 1 end
) as int8
) as square_meters
from SensorLogEntry sle inner join Job job on sle."job" = job."id"
inner join OrderStep os on job."orderStep" = os."id"
inner join cb_order order1 on os."order_id" = order1."id"
inner join Template template2 on order1."template" = template2."id"
left outer join Product product1 on template2."id" = product1."id",
Template template1 cross join Product product2 inner join Template template3 on product2."id" = template3."id",
TechnologyCard tc,
BlankSizeParameterGroup bspg
where order1."template" = template1."id"
and product2."id" = tc."product"
and tc."bl_size_fk" = bspg."id"
and (
template2."id"=product2."id"
or case when product1."id" is not null then 1
when template2."id" is not null then 0
end <>1
and product2."id"=(select max(product3."id") from Product product3 inner join Template template4 on product3."id"=template4."id")
)
group by sle."shiftDate"
order by sle."shiftDate";
[application/gzip] database.dump.gz (4.4M, ../../5124D2BE.5090001@gmail.com/4-database.dump.gz)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-20 15:45 bricklen <bricklen@gmail.com>
parent: Claude Speed <gta3user@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: bricklen @ 2013-02-20 15:45 UTC (permalink / raw)
To: Claude Speed <gta3user@gmail.com>; +Cc: pgsql-bugs
On Wed, Feb 20, 2013 at 5:42 AM, Claude Speed <gta3user@gmail.com> wrote:
> Postgresql 9.2.3 is processing my query is much longer than Postgresql
> 9.1.8:
> Postgresql 9.1.8 - 2292 ms
> Postgresql 9.2.3 - 163336 ms
>
> I provided my query in attach and the database dump too,
> this bug is reproducible.
1). Can you supply the EXPLAIN ANALYZE plans for both queries?
2). Have you ANALYZEd the relevant tables recently?
3). Maybe supply the results of this query too:
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override')
UNION ALL
SELECT 'version' as name, version(), null;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-21 08:55 Claude Speed <gta3user@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Claude Speed @ 2013-02-21 08:55 UTC (permalink / raw)
To: pgsql-bugs
20.02.2013 19:45, bricklen пишет:
> On Wed, Feb 20, 2013 at 5:42 AM, Claude Speed<gta3user@gmail.com> wrote:
>> Postgresql 9.2.3 is processing my query is much longer than Postgresql
>> 9.1.8:
>> Postgresql 9.1.8 - 2292 ms
>> Postgresql 9.2.3 - 163336 ms
>>
>> I provided my query in attach and the database dump too,
>> this bug is reproducible.
> 1). Can you supply the EXPLAIN ANALYZE plans for both queries?
> 2). Have you ANALYZEd the relevant tables recently?
> 3). Maybe supply the results of this query too:
>
> SELECT name, current_setting(name), source
> FROM pg_settings
> WHERE source NOT IN ('default', 'override')
> UNION ALL
> SELECT 'version' as name, version(), null;
1) Explains in attach
2) tables are does not analyzed yet
3) Here they are
Postgres 9.2.3:
"application_name" "pgAdmin III - Query Tool" "client"
"bytea_output" "escape" "session"
"client_encoding" "UNICODE" "session"
"client_min_messages" "notice" "session"
"DateStyle" "ISO, DMY" "session"
"default_text_search_config" "pg_catalog.russian" "configuration file"
"lc_messages" "ru_RU.utf-8" "configuration file"
"lc_monetary" "ru_RU.utf-8" "configuration file"
"lc_numeric" "ru_RU.utf-8" "configuration file"
"lc_time" "ru_RU.utf-8" "configuration file"
"listen_addresses" "*" "configuration file"
"log_timezone" "W-SU" "configuration file"
"max_connections" "100" "configuration file"
"max_stack_depth" "2MB" "environment variable"
"port" "5432" "environment variable"
"shared_buffers" "24MB" "configuration file"
"TimeZone" "W-SU" "configuration file"
"version" "PostgreSQL 9.2.3 on x86_64-pc-linux-gnu, compiled by
x86_64-pc-linux-gnu-gcc (Gentoo 4.5.4 p1.0, pie-0.4.7) 4.5.4, 64-bit" ""
Postgres 9.1.8:
"application_name" "pgAdmin III - Query Tool" "client"
"bytea_output" "escape" "session"
"client_encoding" "UNICODE" "session"
"client_min_messages" "notice" "session"
"DateStyle" "ISO, DMY" "session"
"default_text_search_config" "pg_catalog.russian" "configuration file"
"lc_messages" "ru_RU.utf-8" "configuration file"
"lc_monetary" "ru_RU.utf-8" "configuration file"
"lc_numeric" "ru_RU.utf-8" "configuration file"
"lc_time" "ru_RU.utf-8" "configuration file"
"listen_addresses" "*" "configuration file"
"log_timezone" "W-SU" "environment variable"
"max_connections" "100" "configuration file"
"max_stack_depth" "2MB" "environment variable"
"port" "5432" "environment variable"
"shared_buffers" "24MB" "configuration file"
"silent_mode" "on" "command line"
"TimeZone" "W-SU" "environment variable"
"version" "PostgreSQL 9.1.8 on x86_64-pc-linux-gnu, compiled by
x86_64-pc-linux-gnu-gcc (Gentoo 4.5.4 p1.0, pie-0.4.7) 4.5.4, 64-bit" ""
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Attachments:
[image/jpeg] psql9.2.3.jpeg (130.0K, ../../5125E0EA.2090509@gmail.com/3-psql9.2.3.jpeg)
download | view image
[image/jpeg] psql9.1.8.jpeg (127.9K, ../../5125E0EA.2090509@gmail.com/4-psql9.1.8.jpeg)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-21 17:25 Jeff Janes <jeff.janes@gmail.com>
parent: Claude Speed <gta3user@gmail.com>
1 sibling, 2 replies; 8+ messages in thread
From: Jeff Janes @ 2013-02-21 17:25 UTC (permalink / raw)
To: Claude Speed <gta3user@gmail.com>; +Cc: pgsql-bugs
On Wed, Feb 20, 2013 at 5:42 AM, Claude Speed <gta3user@gmail.com> wrote:
> Hi all,
>
> Postgresql 9.2.3 is processing my query is much longer than Postgresql
> 9.1.8:
> Postgresql 9.1.8 - 2292 ms
> Postgresql 9.2.3 - 163336 ms
>
> I provided my query in attach and the database dump too,
> this bug is reproducible.
>
> Operating system: Gentoo x86_64 Linux 2.6.36
The slowness was introduced with this:
commit e2fa76d80ba571d4de8992de6386536867250474
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Jan 27 19:26:38 2012 -0500
Use parameterized paths to generate inner indexscans more flexibly.
Do you know of simpler queries that also exhibits this behavior? It
would certainly make analysis of the issue easier.
Cheers,
Jeff
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-22 07:54 Claude Speed <gta3user@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Claude Speed @ 2013-02-22 07:54 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-bugs
21.02.2013 21:25, Jeff Janes пишет:
> On Wed, Feb 20, 2013 at 5:42 AM, Claude Speed <gta3user@gmail.com> wrote:
>> Hi all,
>>
>> Postgresql 9.2.3 is processing my query is much longer than Postgresql
>> 9.1.8:
>> Postgresql 9.1.8 - 2292 ms
>> Postgresql 9.2.3 - 163336 ms
>>
>> I provided my query in attach and the database dump too,
>> this bug is reproducible.
>>
>> Operating system: Gentoo x86_64 Linux 2.6.36
> The slowness was introduced with this:
>
> commit e2fa76d80ba571d4de8992de6386536867250474
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Fri Jan 27 19:26:38 2012 -0500
>
> Use parameterized paths to generate inner indexscans more flexibly.
>
>
> Do you know of simpler queries that also exhibits this behavior? It
> would certainly make analysis of the issue easier.
>
>
> Cheers,
>
> Jeff
We was trying to find simple query, sadly unlucky. Only this query is
reproducible.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-22 18:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2013-02-22 18:34 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Claude Speed <gta3user@gmail.com>; pgsql-bugs
Jeff Janes <jeff.janes@gmail.com> writes:
> The slowness was introduced with this:
> Use parameterized paths to generate inner indexscans more flexibly.
Try increasing from_collapse_limit to 11 or more.
I'm not exactly sure why the param-path patch might have changed this
behavior, but in any case the collapse limits ought to be the first
thing you think of when you see a crappy plan for a many-relation query.
The curious join nesting in this query makes me wonder if perhaps it was
hand-optimized to fit the behavior of some old version of the planner ...
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 8+ messages in thread
* new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-23 16:02 Jeff Janes <jeff.janes@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2013-02-23 16:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Claude Speed <gta3user@gmail.com>; pgsql-bugs
On Friday, February 22, 2013, Tom Lane wrote:
> Jeff Janes <jeff.janes@gmail.com> writes:
> > The slowness was introduced with this:
> > Use parameterized paths to generate inner indexscans more flexibly.
>
> Try increasing from_collapse_limit to 11 or more.
>
I've increased it to 20 and still no luck.
>
> I'm not exactly sure why the param-path patch might have changed this
> behavior, but in any case the collapse limits ought to be the first
> thing you think of when you see a crappy plan for a many-relation query.
>
I've stripped it down to this:
explain (analyze,buffers)
select 1 as shift_date
from cb_order order1
inner join Template template2 on order1."template" =
template2."id"
left outer join Product product1 on template2."id" =
product1."id",
Template template1 cross join Product
product2
where order1."template" = template1."id"
and (
template2."id"=product2."id"
or
case when product1."id" is not null
then 1
when template2."id" is not null
then 0
end <>1
and
product2."id"=2916353
) ;
The good plan uses a BitmapOr on a product2.id index to satisfy "
template2.id=product2.id or product2.id= 2916353" (which then needs to use
a filter to check that the CASE part holds in case the true branch of the
OR was the branch with 2916353)
The bad plan seems to have forgotten how to do that, and so seq scans
product2 repeatedly. If I remove the CASE, then it uses the BitmapOr, so
what it has forgotten seems to be that A or (B and C) can only be true if
(A or C) is true.
I say "forgot", because the planner knows that the bad plan is way worse
than the good one, so it is probably about a lack-of-proof-of-correctness
rather than some small change in cost estimation pushing one over the other.
But it isn't as simple as that, as if I replace the CASE with one that
doesn't refer to product1.id, then it relearns how to use the BitmapOr.
case when random()<0.5 then 1
when template2."id" is not null then 0
end <>1
I'm not sure where to go from here.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: new BUG: "postgresql 9.2.3: very long query time"
@ 2013-02-23 16:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2013-02-23 16:25 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Claude Speed <gta3user@gmail.com>; pgsql-bugs
Jeff Janes <jeff.janes@gmail.com> writes:
> On Friday, February 22, 2013, Tom Lane wrote:
>> Try increasing from_collapse_limit to 11 or more.
> I've increased it to 20 and still no luck.
Huh --- that improved the results for me.
> I've stripped it down to this:
I'm not sure if that's exactly the same issue the OP is hitting, but
will take a look at it. Thanks for the simpler test case.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2013-02-23 16:25 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-20 13:42 new BUG: "postgresql 9.2.3: very long query time" Claude Speed <gta3user@gmail.com>
2013-02-20 15:45 ` bricklen <bricklen@gmail.com>
2013-02-21 17:25 ` Jeff Janes <jeff.janes@gmail.com>
2013-02-22 07:54 ` Claude Speed <gta3user@gmail.com>
2013-02-22 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-02-23 16:02 ` Jeff Janes <jeff.janes@gmail.com>
2013-02-23 16:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-02-21 08:55 Re: new BUG: "postgresql 9.2.3: very long query time" Claude Speed <gta3user@gmail.com>
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