From: Claude Speed <gta3user@gmail.com>
To: pgsql-bugs@postgresql.org
Subject: new BUG: "postgresql 9.2.3: very long query time"
Date: Wed, 20 Feb 2013 17:42:22 +0400
Message-ID: <5124D2BE.5090001@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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-bugsAttachments:
[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
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-bugs@postgresql.org
Cc: gta3user@gmail.com
Subject: Re: new BUG: "postgresql 9.2.3: very long query time"
In-Reply-To: <5124D2BE.5090001@gmail.com>
* 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