Re: TB-sized databases
От | Ron Mayer |
---|---|
Тема | Re: TB-sized databases |
Дата | |
Msg-id | 47E069A2.3050801@cheapcomplexdevices.com обсуждение исходный текст |
Ответ на | Re: TB-sized databases (Ron Mayer <rm_pg@cheapcomplexdevices.com>) |
Список | pgsql-performance |
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=1loops=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=1loops=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.859rows=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.667rows=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.135rows=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.888rows=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=#
В списке pgsql-performance по дате отправления:
Предыдущее
От: Craig RingerДата:
Сообщение: Re: What is the best way to storage music files in Postgresql
Следующее
От: Gregory YoungbloodДата:
Сообщение: Re: What is the best way to storage music files in Postgresql