[rt-devel] Behaviour correction for SB-0.88 (improves Oracle support)

Paul Wagland pwagland at allshare.nl
Thu Jul 17 05:35:20 EDT 2003


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





More information about the Rt-devel mailing list