Forum OpenACS Development: Re: Lacking views in CR

Collapse
6: Re: Lacking views in CR (response to 1)
Posted by Dave Bauer on 02/28/05 04:24 PM
Strange. I get the exact opposite results with a default PostgreSQL install:

The view:

explain analyze SELECT count(*)
FROM cr_items ci, cr_revisions cr
WHERE ci.item_id = cr.item_id AND
ci.live_revision = cr.revision_id ;
QUERY PLAN
-------------------------------------------------------------------------------\
---------------------------------------------------
 Aggregate  (cost=2192.73..2192.73 rows=1 width=0) (actual time=575.284..575.286 rows=1 loops=1)
   ->  Hash Join  (cost=622.84..2192.72 rows=1 width=0) (actual time=129.234..532.460 rows=16350 loops=1)
         Hash Cond: (("outer".item_id = "inner".item_id) AND ("outer".revision_id = "inner".live_revision))
         ->  Seq Scan on cr_revisions cr  (cost=0.00..806.97 rows=18397 width=8) (actual time=0.081..72.097 rows=18403 loops=1)
         ->  Hash  (cost=470.89..470.89 rows=16989 width=8) (actual time=128.085..128.085 rows=0 loops=1)
               ->  Seq Scan on cr_items ci  (cost=0.00..470.89 rows=16989 width=8) (actual time=0.080..75.305 rows=16992 loops=1)
 Total runtime: 576.018 ms
(7 rows)
explain analyze SELECT count(*)
FROM cr_items ci, cr_revisions cr
WHERE ci.live_revision = cr.revision_id ;
                                                            QUERY PLAN
-------------------------------------------------------------------------------\
----------------------------------------------------
 Aggregate  (cost=2045.51..2045.51 rows=1 width=0) (actual time=2722.408..2722.410 rows=1 loops=1)
   ->  Hash Join  (cost=572.36..2004.47 rows=16418 width=0) (actual time=219.867..1689.530 rows=16352 loops=1)
         Hash Cond: ("outer".revision_id = "inner".live_revision)
         ->  Seq Scan on cr_revisions cr  (cost=0.00..806.97 rows=18397 width=4) (actual time=0.069..174.145 rows=18403 loops=1)
         ->  Hash  (cost=470.89..470.89 rows=16989 width=4) (actual time=219.544..219.544 rows=0 loops=1)
               ->  Seq Scan on cr_items ci  (cost=0.00..470.89 rows=16989 width=4) (actual time=0.076..151.643 rows=16992 loops=1)
 Total runtime: 2722.725 ms
(7 rows)
Also you do not need to specify cr.item_id=ci.item_id in the first query. Where cr.revision_id=ci.live_revision will only return at mot one row.
Collapse
7: Re: Re: Lacking views in CR (response to 6)
Posted by Nis Jørgensen on 02/28/05 06:57 PM
Dave, your result is not so strange, since the foreign key reference is not present in postgres.

My guess is that adding the simple rreference will give the same results as Oracle, while the multi-field version will make them behave identically.

Regarding the last comment: That is the entire point, right? You don't need the extra condition, but in many cases you inherit it from underlying views. And it slows things down for Oracle.

/Nis