From: | Chris <dmagick(at)gmail(dot)com> |
---|---|
To: | Evan Carroll <lists(at)evancarroll(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: left join + case - how is it processed? |
Date: | 2009-01-19 05:05:00 |
Message-ID: | 497409FC.6000603@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
>
>> I thought the where condition would cut down on the rows returned, then the
>> case statement would take effect to do the null check. It seems to be doing
>> it in reverse ??
> # explain analyze SELECT * from sq_vw_ast_perm where assetid='30748';
>
> It aperas to me that both of your statements have where clauses, but I
> believe where isn't that explicit. I'm not sure the nature of your
> problem permits the query optimizer to eliminate rows at all, even
> with the where statement. "assetid" is probably not known when the
> query optimizer hits, because it is computed based on the nullness of
> the columns. I'd assume that the optimizer *could* more easily
> optimize this if you had used coalesce rather than an ad-hoc method
> with CASE. My guess is you can exclude rows with WHERE if the the
> column used is an run-time computation involving an ad-hoc CASE.
No difference.
Full explain plan here:
http://explain-analyze.info/query_plans/2725-query-plan-1447
I can see it's doing the extra filter step at the start (4th line) which
is not present without the coalesce/case statement. I just don't
understand why it's being done at that stage.
--
Postgresql & php tutorials
http://www.designmagick.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-01-19 16:33:34 | Re: left join + case - how is it processed? |
Previous Message | Chris | 2009-01-19 04:36:13 | Re: left join + case - how is it processed? |