Tom Lane wrote:
> I'm having a hard time getting excited about improving this query when
> it's so badly coded in the first place. What's an ORDER BY doing in
> an EXISTS subquery? The LIMIT is unnecessary too. And the inner WHERE
> says nothing so much as "I don't know how to design a database" :-(.
It was the test query which has the same execution plan for belg_mobile
(and the same problem) as the production query below:
select (select max(code) from belg_mobile tc where c.bnum >= tc.code and c.bnum like tc.code || '%') as code,
c.cause,c.ilno, extract(hour from c.datetime) as hour, count(*) as cnt, sum(c.dur) as dur
from conn.conn20060803 c
where itgrp = :itgrp
group by 1,2,3,4
It's a simple OLAP query for analysis telephonic traffic distribution
over time and trunk codes.
'max(codes)' is used to get the most matching code. For example,
84725 and 8472 are both valid codes, and number 84725123456 must match
84725 but not 8472. The 'c.bnum >= tc.code' qual significantly reduce
index scan and execution time.