[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] TB-sized databases
From:       Ron Mayer <rm_pg () cheapcomplexdevices ! com>
Date:       2008-03-19 1:17:22
Message-ID: 47E069A2.3050801 () cheapcomplexdevices ! com
[Download RAW message or body]

Ron Mayer wrote:
> Tom Lane wrote:
> > Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> > > Would another possible condition for considering
> > > Cartesian joins be be:
> > > * Consider Cartesian joins when a unique constraint can prove
> > > that at most one row will be pulled from one of the tables
> > > that would be part of this join?
> > 
> > What for?  That would still lead us to consider large numbers of totally
> > useless joins.
> 
> Often I get order-of-magnitude better queries by forcing the cartesian
> join even without multi-column indexes.

Ah - and sometimes even 2 order of magnitude improvements.

1.1 seconds with Cartesian join, 200 seconds if it
doesn't use it.



logs=# explain analyze select * from fact natural join (select * from d_ref natural \
join d_uag where ref_host = 'www.real.com' and ref_path = \
'/products/player/more_info/moreinfo.html' and ref_query = \
'?ID=370&DC=&LANG=&PN=RealOne%20Player&PV=6.0.11.818&PT=&OS=&CM=&CMV=&LS=&RE=&RA=&RV=' \
                and useragent = 'Mozilla/4.0 (compatible; MSIE 5.01; Windows 98)' \
                offset 0 ) as a;
                                                                                      \
                QUERY PLAN
-------------------------------------------------------------------------------------- \
-------------------------------------------------------------------------------------- \
--------------------------------------------------------------------------------------------------
  Nested Loop  (cost=6465.12..7575.91 rows=367 width=2096) (actual \
                time=1118.741..1119.207 rows=122 loops=1)
    ->  Limit  (cost=0.00..14.22 rows=1 width=218) (actual time=0.526..0.542 rows=1 \
                loops=1)
          ->  Nested Loop  (cost=0.00..14.22 rows=1 width=218) (actual \
                time=0.524..0.537 rows=1 loops=1)
                ->  Index Scan using i_ref__val on d_ref  (cost=0.00..7.83 rows=1 \
width=127) (actual time=0.168..0.170 rows=1 loops=1)  Index Cond: (((ref_path)::text \
= '/products/player/more_info/moreinfo.html'::text) AND ((ref_host)::text = \
'www.real.com'::text) AND ((ref_query)::text = \
'?ID=370&DC=&LANG=&PN=RealOne%20Player&PV=6.0.11.818&PT=&OS=&CM=&CMV=&LS=&RE=&RA=&RV='::text))
                
                ->  Index Scan using i_uag__val on d_uag  (cost=0.00..6.38 rows=1 \
                width=91) (actual time=0.347..0.355 rows=1 loops=1)
                      Index Cond: ((useragent)::text = 'Mozilla/4.0 (compatible; MSIE \
                5.01; Windows 98)'::text)
    ->  Bitmap Heap Scan on fact  (cost=6465.12..7556.18 rows=367 width=32) (actual \
                time=1118.196..1118.491 rows=122 loops=1)
          Recheck Cond: ((fact.uag_id = a.uag_id) AND (fact.ref_id = a.ref_id))
          ->  BitmapAnd  (cost=6465.12..6465.12 rows=367 width=0) (actual \
                time=1115.565..1115.565 rows=0 loops=1)
                ->  Bitmap Index Scan on i__fact__uag_id  (cost=0.00..2770.83 \
rows=196223 width=0) (actual time=813.859..813.859 rows=1183470 loops=1)  Index Cond: \
                (fact.uag_id = a.uag_id)
                ->  Bitmap Index Scan on i__fact__ref_id  (cost=0.00..3581.50 \
rows=253913 width=0) (actual time=8.667..8.667 rows=13751 loops=1)  Index Cond: \
(fact.ref_id = a.ref_id)  Total runtime: 1122.245 ms
(15 rows)

logs=# explain analyze select * from fact natural join (select * from d_ref natural \
join d_uag where ref_host = 'www.real.com' and ref_path = \
'/products/player/more_info/moreinfo.html' and ref_query = \
'?ID=370&DC=&LANG=&PN=RealOne%20Player&PV=6.0.11.818&PT=&OS=&CM=&CMV=&LS=&RE=&RA=&RV=' \
                and useragent = 'Mozilla/4.0 (compatible; MSIE 5.01; Windows 98)' ) \
                as a;
                                                                                      \
                QUERY PLAN
-------------------------------------------------------------------------------------- \
-------------------------------------------------------------------------------------- \
--------------------------------------------------------------------------------------------
  Hash Join  (cost=2827.72..398919.05 rows=1 width=242) (actual \
time=114138.193..200622.416 rows=122 loops=1)  Hash Cond: (fact.ref_id = \
                d_ref.ref_id)
    ->  Nested Loop  (cost=2819.88..398908.65 rows=511 width=119) (actual \
                time=1524.600..199522.182 rows=1183470 loops=1)
          ->  Index Scan using i_uag__val on d_uag  (cost=0.00..6.38 rows=1 width=91) \
                (actual time=0.023..0.033 rows=1 loops=1)
                Index Cond: ((useragent)::text = 'Mozilla/4.0 (compatible; MSIE 5.01; \
                Windows 98)'::text)
          ->  Bitmap Heap Scan on fact  (cost=2819.88..396449.49 rows=196223 \
width=32) (actual time=1524.562..197627.135 rows=1183470 loops=1)  Recheck Cond: \
                (fact.uag_id = d_uag.uag_id)
                ->  Bitmap Index Scan on i__fact__uag_id  (cost=0.00..2770.83 \
rows=196223 width=0) (actual time=758.888..758.888 rows=1183470 loops=1)  Index Cond: \
                (fact.uag_id = d_uag.uag_id)
    ->  Hash  (cost=7.83..7.83 rows=1 width=127) (actual time=0.067..0.067 rows=1 \
                loops=1)
          ->  Index Scan using i_ref__val on d_ref  (cost=0.00..7.83 rows=1 \
width=127) (actual time=0.058..0.060 rows=1 loops=1)  Index Cond: (((ref_path)::text \
= '/products/player/more_info/moreinfo.html'::text) AND ((ref_host)::text = \
'www.real.com'::text) AND ((ref_query)::text = \
'?ID=370&DC=&LANG=&PN=RealOne%20Player&PV=6.0.11.818&PT=&OS=&CM=&CMV=&LS=&RE=&RA=&RV='::text))
  Total runtime: 200625.636 ms
(13 rows)

logs=#

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[prev in list] [next in list] [prev in thread] [next in thread]