pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Optimizer Failure / Possible Bug
7+ messages / 3 participants
[nested] [flat]
* Query Optimizer Failure / Possible Bug
@ 2005-03-24 10:31 Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 14:14 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 19:51 ` Re: Query Optimizer Failure / Possible Bug Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Hannes Dorbath @ 2005-03-24 10:31 UTC (permalink / raw)
To: pgsql-performance
The query and the corresponding EXPLAIN is at
http://hannes.imos.net/query.txt
I'd like to use the column q.replaced_serials for multiple calculations
in the SELECT clause, but every time it is referenced there in some way
the whole query in the FROM clause returning q is executed again.
This doesn't make sense to me at all and eats performance.
If this wasn't clear enough, for every
q.replaced_serials <insert_random_calculation> AS some_column
in the SELECT clause there is new block of
---------------------------------------------------------------
-> Aggregate (cost=884.23..884.23 rows=1 width=0)
-> Nested Loop (cost=0.00..884.23 rows=1 width=0)
-> Index Scan using ix_rma_ticket_serials_replace on
rma_ticket_serials rts (cost=0.00..122.35
rows=190 width=4)
Index Cond: ("replace" = false)
-> Index Scan using pk_serials on serials s
(cost=0.00..3.51 rows=1 width=4)
Index Cond: (s.serial_id = "outer".serial_id)
Filter: ((article_no = $0) AND (delivery_id = $1))
---------------------------------------------------------------
in the EXPLAIN result.
For those who wonder why I do this FROM (SELECT...). I was searching for
a way to use the result of an subselect for multiple calculations in the
SELECT clause and return that calculation results as individual columns.
I tested a bit further and found out that PG behaves the same in case q
is a view. This makes me wonder how efficient the optimizer can work
with views - or even worse - nested views.
Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
Thanks in advance,
Hannes Dorbath
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Optimizer Failure / Possible Bug
2005-03-24 10:31 Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
@ 2005-03-28 14:14 ` Hannes Dorbath <light@theendofthetunnel.de>
2005-04-03 08:01 ` Re: Query Optimizer Failure / Possible Bug PFC <lists@boutiquenumerique.com>
1 sibling, 1 reply; 7+ messages in thread
From: Hannes Dorbath @ 2005-03-28 14:14 UTC (permalink / raw)
To: pgsql-performance
hm, a few days and not a single reply :|
any more information needed? test data? simplified test case? anything?
thanks
Hannes Dorbath wrote:
> The query and the corresponding EXPLAIN is at
>
> http://hannes.imos.net/query.txt
>
> I'd like to use the column q.replaced_serials for multiple calculations
> in the SELECT clause, but every time it is referenced there in some way
> the whole query in the FROM clause returning q is executed again.
>
> This doesn't make sense to me at all and eats performance.
>
> If this wasn't clear enough, for every
>
> q.replaced_serials <insert_random_calculation> AS some_column
>
> in the SELECT clause there is new block of
>
> ---------------------------------------------------------------
> -> Aggregate (cost=884.23..884.23 rows=1 width=0)
> -> Nested Loop (cost=0.00..884.23 rows=1 width=0)
> -> Index Scan using ix_rma_ticket_serials_replace on
> rma_ticket_serials rts (cost=0.00..122.35
> rows=190 width=4)
> Index Cond: ("replace" = false)
> -> Index Scan using pk_serials on serials s
> (cost=0.00..3.51 rows=1 width=4)
> Index Cond: (s.serial_id = "outer".serial_id)
> Filter: ((article_no = $0) AND (delivery_id = $1))
> ---------------------------------------------------------------
>
> in the EXPLAIN result.
>
> For those who wonder why I do this FROM (SELECT...). I was searching for
> a way to use the result of an subselect for multiple calculations in the
> SELECT clause and return that calculation results as individual columns.
>
> I tested a bit further and found out that PG behaves the same in case q
> is a view. This makes me wonder how efficient the optimizer can work
> with views - or even worse - nested views.
>
> Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
>
>
> Thanks in advance,
> Hannes Dorbath
--
imos Gesellschaft fuer Internet-Marketing und Online-Services mbH
Alfons-Feifel-Str. 9 // D-73037 Goeppingen // Stauferpark Ost
Tel: 07161 93339-14 // Fax: 07161 93339-99 // Internet: www.imos.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Optimizer Failure / Possible Bug
2005-03-24 10:31 Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 14:14 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
@ 2005-04-03 08:01 ` PFC <lists@boutiquenumerique.com>
2005-04-04 15:18 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
0 siblings, 1 reply; 7+ messages in thread
From: PFC @ 2005-04-03 08:01 UTC (permalink / raw)
To: Hannes Dorbath <light@theendofthetunnel.de>; pgsql-performance
Noticed this problem,too.
You can always make the calculation you want done once inside a set
returning function so it'll behave like a table, but that's ugly.
On Mon, 28 Mar 2005 16:14:44 +0200, Hannes Dorbath
<light@theendofthetunnel.de> wrote:
> hm, a few days and not a single reply :|
>
> any more information needed? test data? simplified test case? anything?
>
>
> thanks
>
>
> Hannes Dorbath wrote:
>> The query and the corresponding EXPLAIN is at
>> http://hannes.imos.net/query.txt
>> I'd like to use the column q.replaced_serials for multiple calculations
>> in the SELECT clause, but every time it is referenced there in some way
>> the whole query in the FROM clause returning q is executed again.
>> This doesn't make sense to me at all and eats performance.
>> If this wasn't clear enough, for every
>> q.replaced_serials <insert_random_calculation> AS some_column
>> in the SELECT clause there is new block of
>> ---------------------------------------------------------------
>> -> Aggregate (cost=884.23..884.23 rows=1 width=0)
>> -> Nested Loop (cost=0.00..884.23 rows=1 width=0)
>> -> Index Scan using ix_rma_ticket_serials_replace on
>> rma_ticket_serials rts (cost=0.00..122.35
>> rows=190 width=4)
>> Index Cond: ("replace" = false)
>> -> Index Scan using pk_serials on serials s
>> (cost=0.00..3.51 rows=1 width=4)
>> Index Cond: (s.serial_id = "outer".serial_id)
>> Filter: ((article_no = $0) AND (delivery_id = $1))
>> ---------------------------------------------------------------
>> in the EXPLAIN result.
>> For those who wonder why I do this FROM (SELECT...). I was searching
>> for
>> a way to use the result of an subselect for multiple calculations in the
>> SELECT clause and return that calculation results as individual columns.
>> I tested a bit further and found out that PG behaves the same in case q
>> is a view. This makes me wonder how efficient the optimizer can work
>> with views - or even worse - nested views.
>> Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
>> Thanks in advance,
>> Hannes Dorbath
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Optimizer Failure / Possible Bug
2005-03-24 10:31 Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 14:14 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-04-03 08:01 ` Re: Query Optimizer Failure / Possible Bug PFC <lists@boutiquenumerique.com>
@ 2005-04-04 15:18 ` Hannes Dorbath <light@theendofthetunnel.de>
2005-04-16 10:45 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
0 siblings, 1 reply; 7+ messages in thread
From: Hannes Dorbath @ 2005-04-04 15:18 UTC (permalink / raw)
To: pgsql-performance
Mhh. I have no clue about the internals of PostgreSQL and query planing,
but to me as user this should really be a thing the optimizer has to
work out..
On 03.04.2005 10:01, PFC wrote:
>
> Noticed this problem,too.
> You can always make the calculation you want done once inside a set
> returning function so it'll behave like a table, but that's ugly.
>
> On Mon, 28 Mar 2005 16:14:44 +0200, Hannes Dorbath
> <light@theendofthetunnel.de> wrote:
>
>> hm, a few days and not a single reply :|
>>
>> any more information needed? test data? simplified test case? anything?
>>
>>
>> thanks
>>
>>
>> Hannes Dorbath wrote:
>>
>>> The query and the corresponding EXPLAIN is at
>>> http://hannes.imos.net/query.txt
>>> I'd like to use the column q.replaced_serials for multiple calculations
>>> in the SELECT clause, but every time it is referenced there in some way
>>> the whole query in the FROM clause returning q is executed again.
>>> This doesn't make sense to me at all and eats performance.
>>> If this wasn't clear enough, for every
>>> q.replaced_serials <insert_random_calculation> AS some_column
>>> in the SELECT clause there is new block of
>>> ---------------------------------------------------------------
>>> -> Aggregate (cost=884.23..884.23 rows=1 width=0)
>>> -> Nested Loop (cost=0.00..884.23 rows=1 width=0)
>>> -> Index Scan using ix_rma_ticket_serials_replace on
>>> rma_ticket_serials rts (cost=0.00..122.35
>>> rows=190 width=4)
>>> Index Cond: ("replace" = false)
>>> -> Index Scan using pk_serials on serials s
>>> (cost=0.00..3.51 rows=1 width=4)
>>> Index Cond: (s.serial_id = "outer".serial_id)
>>> Filter: ((article_no = $0) AND (delivery_id = $1))
>>> ---------------------------------------------------------------
>>> in the EXPLAIN result.
>>> For those who wonder why I do this FROM (SELECT...). I was
>>> searching for
>>> a way to use the result of an subselect for multiple calculations in the
>>> SELECT clause and return that calculation results as individual columns.
>>> I tested a bit further and found out that PG behaves the same in case q
>>> is a view. This makes me wonder how efficient the optimizer can work
>>> with views - or even worse - nested views.
>>> Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
>>> Thanks in advance,
>>> Hannes Dorbath
>>
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Optimizer Failure / Possible Bug
2005-03-24 10:31 Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 14:14 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-04-03 08:01 ` Re: Query Optimizer Failure / Possible Bug PFC <lists@boutiquenumerique.com>
2005-04-04 15:18 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
@ 2005-04-16 10:45 ` Hannes Dorbath <light@theendofthetunnel.de>
0 siblings, 0 replies; 7+ messages in thread
From: Hannes Dorbath @ 2005-04-16 10:45 UTC (permalink / raw)
To: pgsql-performance
Some people on the #postgresql irc channel pointed out that it's a known
issue.
http://www.qaix.com/postgresql-database-development/246-557-select-based-on-function-result-read.sht...
A more simple testcase is below. Adding OFFSET 0 to the inner query does
indeed fix it in my case.
SELECT
tmp.user_id AS foo,
tmp.user_id AS bar,
tmp.user_id AS baz
FROM
(
SELECT
u.user_id
FROM
users u
) AS tmp;
Seq Scan on users (cost=0.00..1.53 rows=53 width=4) (actual
time=0.230..0.233 rows=1 loops=1)
Total runtime: 0.272 ms
---------------------------
SELECT
tmp.user_id AS foo,
tmp.user_id AS bar,
tmp.user_id AS baz
FROM
(
SELECT
(SELECT 1) AS user_id
FROM
users u
) AS tmp;
Seq Scan on users u (cost=0.03..1.56 rows=53 width=0) (actual
time=0.216..0.219 rows=1 loops=1)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.004..0.006 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.002..0.004 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.002..0.003 rows=1 loops=1)
Total runtime: 0.270 ms
---------------------------
SELECT
tmp.user_id AS foo,
tmp.user_id AS bar,
tmp.user_id AS baz
FROM
(
SELECT
(SELECT 1) AS user_id
FROM
users u
OFFSET 0
) AS tmp;
Subquery Scan tmp (cost=0.01..1.03 rows=1 width=4) (actual
time=0.032..0.042 rows=1 loops=1)
-> Limit (cost=0.01..1.02 rows=1 width=0) (actual time=0.026..0.033
rows=1 loops=1)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.003..0.004 rows=1 loops=1)
-> Seq Scan on users u (cost=0.00..1.01 rows=1 width=0)
(actual time=0.022..0.027 rows=1 loops=1)
Total runtime: 0.090 ms
On 04.04.2005 17:18, Hannes Dorbath wrote:
> Mhh. I have no clue about the internals of PostgreSQL and query planing,
> but to me as user this should really be a thing the optimizer has to
> work out..
>
>
> On 03.04.2005 10:01, PFC wrote:
>
>>
>> Noticed this problem,too.
>> You can always make the calculation you want done once inside a
>> set returning function so it'll behave like a table, but that's ugly.
>>
>> On Mon, 28 Mar 2005 16:14:44 +0200, Hannes Dorbath
>> <light@theendofthetunnel.de> wrote:
>>
>>> hm, a few days and not a single reply :|
>>>
>>> any more information needed? test data? simplified test case? anything?
>>>
>>>
>>> thanks
>>>
>>>
>>> Hannes Dorbath wrote:
>>>
>>>> The query and the corresponding EXPLAIN is at
>>>> http://hannes.imos.net/query.txt
>>>> I'd like to use the column q.replaced_serials for multiple
>>>> calculations
>>>> in the SELECT clause, but every time it is referenced there in some way
>>>> the whole query in the FROM clause returning q is executed again.
>>>> This doesn't make sense to me at all and eats performance.
>>>> If this wasn't clear enough, for every
>>>> q.replaced_serials <insert_random_calculation> AS some_column
>>>> in the SELECT clause there is new block of
>>>> ---------------------------------------------------------------
>>>> -> Aggregate (cost=884.23..884.23 rows=1 width=0)
>>>> -> Nested Loop (cost=0.00..884.23 rows=1 width=0)
>>>> -> Index Scan using ix_rma_ticket_serials_replace on
>>>> rma_ticket_serials rts (cost=0.00..122.35
>>>> rows=190 width=4)
>>>> Index Cond: ("replace" = false)
>>>> -> Index Scan using pk_serials on serials s
>>>> (cost=0.00..3.51 rows=1 width=4)
>>>> Index Cond: (s.serial_id = "outer".serial_id)
>>>> Filter: ((article_no = $0) AND (delivery_id = $1))
>>>> ---------------------------------------------------------------
>>>> in the EXPLAIN result.
>>>> For those who wonder why I do this FROM (SELECT...). I was
>>>> searching for
>>>> a way to use the result of an subselect for multiple calculations in
>>>> the
>>>> SELECT clause and return that calculation results as individual
>>>> columns.
>>>> I tested a bit further and found out that PG behaves the same in
>>>> case q
>>>> is a view. This makes me wonder how efficient the optimizer can work
>>>> with views - or even worse - nested views.
>>>> Tested and reproduced on PG 7.4.1 linux and 8.0.0 win32.
>>>> Thanks in advance,
>>>> Hannes Dorbath
>>>
>>>
>>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Optimizer Failure / Possible Bug
2005-03-24 10:31 Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
@ 2005-03-28 19:51 ` Josh Berkus <josh@agliodbs.com>
2005-03-29 00:15 ` Re: Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
1 sibling, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2005-03-28 19:51 UTC (permalink / raw)
To: Hannes Dorbath <light@theendofthetunnel.de>; +Cc: pgsql-performance
Hannes,
> The query and the corresponding EXPLAIN is at
>
> http://hannes.imos.net/query.txt
The problem is that you're using a complex corellated sub-select in the SELECT
clause:
SELECT
d.delivery_id,
da.article_no,
da.amount,
(
SELECT
COUNT(*)
FROM
serials s
INNER JOIN rma_ticket_serials rts ON (
s.serial_id = rts.serial_id
)
WHERE
s.article_no = da.article_no AND
s.delivery_id = d.delivery_id AND
rts.replace = FALSE
) AS replaced_serials
This means that the planner pretty much has to iterate over the subquery,
running it once for each row in the result set. If you want the optimizer
to use a JOIN structure instead, put the subselect in the FROM clause.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Optimizer Failure / Possible Bug
2005-03-24 10:31 Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 19:51 ` Re: Query Optimizer Failure / Possible Bug Josh Berkus <josh@agliodbs.com>
@ 2005-03-29 00:15 ` Hannes Dorbath <light@theendofthetunnel.de>
0 siblings, 0 replies; 7+ messages in thread
From: Hannes Dorbath @ 2005-03-29 00:15 UTC (permalink / raw)
To: pgsql-performance
Thank you very much for your reply. I'll try to modify it.
Josh Berkus wrote:
> Hannes,
>
>
>>The query and the corresponding EXPLAIN is at
>>
>>http://hannes.imos.net/query.txt
>
>
> The problem is that you're using a complex corellated sub-select in the SELECT
> clause:
>
> SELECT
> d.delivery_id,
> da.article_no,
> da.amount,
> (
> SELECT
> COUNT(*)
> FROM
> serials s
> INNER JOIN rma_ticket_serials rts ON (
> s.serial_id = rts.serial_id
> )
> WHERE
> s.article_no = da.article_no AND
> s.delivery_id = d.delivery_id AND
> rts.replace = FALSE
> ) AS replaced_serials
>
> This means that the planner pretty much has to iterate over the subquery,
> running it once for each row in the result set. If you want the optimizer
> to use a JOIN structure instead, put the subselect in the FROM clause.
>
--
imos Gesellschaft fuer Internet-Marketing und Online-Services mbH
Alfons-Feifel-Str. 9 // D-73037 Goeppingen // Stauferpark Ost
Tel: 07161 93339-14 // Fax: 07161 93339-99 // Internet: www.imos.net
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-04-16 10:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-24 10:31 Query Optimizer Failure / Possible Bug Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 14:14 ` Hannes Dorbath <light@theendofthetunnel.de>
2005-04-03 08:01 ` PFC <lists@boutiquenumerique.com>
2005-04-04 15:18 ` Hannes Dorbath <light@theendofthetunnel.de>
2005-04-16 10:45 ` Hannes Dorbath <light@theendofthetunnel.de>
2005-03-28 19:51 ` Josh Berkus <josh@agliodbs.com>
2005-03-29 00:15 ` Hannes Dorbath <light@theendofthetunnel.de>
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