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/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
