Re: Should the optimiser convert a CASE into a WHERE if it can?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Should the optimiser convert a CASE into a WHERE if it can?
Date: 2010-01-26 17:21:38
Message-ID: 15148.1264526498@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Richard Neill <rn214(at)cam(dot)ac(dot)uk> writes:
> SELECT
> SUM (case when id > 1200000 and id < 1210000 then 1 else 0 end) AS c1,
> SUM (case when id > 1210000 and id < 1220000 then 1 else 0 end) AS c2,
> ...
> FROM tbl_tracker;

> This can be manually optimised into a far uglier (but much much faster)
> query:

> SELECT * FROM
> (SELECT COUNT (1) AS c1 FROM tbl_tracker
> WHERE id > 1200000 and id < 1210000) AS s1,
> (SELECT COUNT (1) AS c2 FROM tbl_tracker
> WHERE id > 1210000 and id < 1220000) AS s2,
> ...

We're unlikely to consider doing this, for a couple of reasons:
it's unlikely to come up often enough to justify the cycles the planner
would spend looking for the case *on every query*, and it requires very
special knowledge about the behavior of two specific aggregate functions,
which is something the planner tends to avoid using.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Matthew Wakeling 2010-01-26 17:23:06 Re: Should the optimiser convert a CASE into a WHERE if it can?
Previous Message Richard Neill 2010-01-26 17:10:26 Should the optimiser convert a CASE into a WHERE if it can?