Forum OpenACS Development: Re: Best way to join with latest revisions

Dave,

here is an example using a view wich in turns uses the CR auto generated view:

oacs51=# explain select x.item_id,
               title, 
               upper_title, 
               product_code,
               x.is_active_p,
               u.um_code,
               x.brand_id,
               x.category_id
        from  mis_active_products x, mis_um u
        where  x.item_id = 1 and
               x.um_id   = u.um_id;
QUERY PLAN

 Nested Loop  (cost=24.94..31.10 rows=5 width=647)
   ->  Seq Scan on cr_text  (cost=0.00..1.01 rows=1 width=0)
   ->  Nested Loop  (cost=24.94..30.05 rows=4 width=647)
         ->  Index Scan using cr_items_pk on cr_items i  (cost=0.00..4.99 rows=2 width=4)
               Index Cond: (1 = item_id)
         ->  Materialize  (cost=24.94..24.97 rows=3 width=647)
               ->  Nested Loop  (cost=5.00..24.94 rows=3 width=647)
                     ->  Nested Loop  (cost=5.00..20.10 rows=1 width=638)
                           ->  Nested Loop  (cost=5.00..15.27 rows=1 width=43)
                                 ->  Hash Join  (cost=5.00..11.75 rows=1 width=39)
                                       Hash Cond: ("outer".revision_id = "inner".live_revision)
                                       ->  Index Scan using cr_revisions_item_id_idx on cr_revisions cr  (cost=0.00..6.73 rows=2 width=35)
                                             Index Cond: (item_id = 1)
                                       ->  Hash  (cost=4.99..4.99 rows=2 width=8)
                                             ->  Index Scan using cr_items_pk on cr_items ci  (cost=0.00..4.99 rows=2 width=8)
                                                   Index Cond: (1 = item_id)
                                 ->  Index Scan using acs_objects_pk on acs_objects  (cost=0.00..3.50 rows=1 width=4)
                                       Index Cond: ("outer".live_revision = acs_objects.object_id)
                           ->  Index Scan using mis_products_pkey on mis_products  (cost=0.00..4.82 rows=1 width=611)
                                 Index Cond: ("outer".live_revision = mis_products.product_id)
                     ->  Index Scan using mis_um_pkey on mis_um u  (cost=0.00..4.82 rows=1 width=17)
                           Index Cond: ("outer".um_id = u.um_id)
(22 rows)

Avoiding the CR generated view things are much better:

oacs51=# explain select x.item_id, 
               title,
               upper_title, 
               product_code,
               x.is_active_p,
               u.um_code,
               x.brand_id,
               x.category_id
        from  mis_fast_products x, mis_um u
        where  x.item_id = 1 and
               x.um_id   = u.um_id;
 QUERY PLAN

 Nested Loop  (cost=0.00..36.46 rows=3 width=647)
   ->  Nested Loop  (cost=0.00..26.79 rows=2 width=638)
         ->  Nested Loop  (cost=0.00..17.12 rows=2 width=39)
               ->  Index Scan using cr_revisions_item_id_idx on cr_revisions cr  (cost=0.00..6.73 rows=2 width=35)
                     Index Cond: (item_id = 1)
               ->  Index Scan using cr_items_by_live_revision on cr_items ci  (cost=0.00..5.18 rows=1 width=4)
                     Index Cond: ("outer".revision_id = ci.live_revision)
         ->  Index Scan using mis_products_pkey on mis_products p  (cost=0.00..4.82 rows=1 width=611)
               Index Cond: (p.product_id = "outer".revision_id)
   ->  Index Scan using mis_um_pkey on mis_um u  (cost=0.00..4.82 rows=1 width=17)
         Index Cond: ("outer".um_id = u.um_id)
(11 rows)

You are right. I don't join with acs_objects because in most cases I don't need it and this makes a huge difference in performance terms.