Ron Mayer wrote:
Tom Lane wrote:
Ron Mayer <rm_pg@xxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
