Figured I would chime in... I can't say whether the change is correct or
not, will look into testing this next Monday... off to Frankfurt for the REM
concert in the meantime ;-)
But with postgres, this statement appears to be true:
> return true. This causes problems for the Oracle implementation with
> SQL being generated:
>
> SELECT main.* FROM ( SELECT DISTINCT main.id FROM Queues main )
> distinctquery, Queues main WHERE (main.id = distinctquery.id) AND
> ((main.Disabled = '0'))
>
> rather than the more simpler:
>
> SELECT main.* FROM Queues main WHERE (main.Disabled = '0')
>
> so I suggest the following patch to SB.pm - which could reduce the
> overhead for other databases as well.
Here is the explain texts for these. (We have 18 queues, 1 of which is
disabled). Mind you, this probably won't make a huge difference in the
interface... but every little bit helps I guess...
rt3=# explain SELECT main.* FROM ( SELECT DISTINCT main.id FROM Queues
main ) distinctquery, Queues main WHERE (main.id = distinctquery.id) AND
((main.Disabled = '0'));
QUERY PLAN
----------------------------------------------------------------------------
--------------
Hash Join (cost=1.65..2.96 rows=1 width=116)
Hash Cond: ("outer".id = "inner".id)
-> Seq Scan on queues main (cost=0.00..1.23 rows=17 width=112)
Filter: (disabled = 0::smallint)
-> Hash (cost=1.65..1.65 rows=2 width=4)
-> Subquery Scan distinctquery (cost=1.56..1.65 rows=2 width=4)
-> Unique (cost=1.56..1.65 rows=2 width=4)
-> Sort (cost=1.56..1.60 rows=18 width=4)
Sort Key: id
-> Seq Scan on queues main (cost=0.00..1.18
rows=18 width=4)
(10 rows)
rt3=# explain SELECT main.* FROM Queues main WHERE (main.Disabled = '0');
QUERY PLAN
--------------------------------------------------------------
Seq Scan on queues main (cost=0.00..1.23 rows=17 width=112)
Filter: (disabled = 0::smallint)
(2 rows)
Cheers,
Paul