Как анализировать результат explain?

[CrazyHacKeRs]

Как анализировать результат explain?

Подскажите куда смотреть, и что видно по след. даным:
Что нужно оптимизировать?

Hash Join  (cost=1579.54..6096623.52 rows=102 width=1926)
  Hash Cond: ("outer".image_id = "inner".image_id)
  ->  Nested Loop  (cost=1339.26..6096358.38 rows=86 width=1922)
        ->  Hash Join  (cost=1339.26..6096184.72 rows=86 width=1890)
              Hash Cond: ("outer".item_id = "inner".item_id)
              ->  Hash Join  (cost=7.60..6094616.50 rows=1777 width=1882)
                    Hash Cond: ("outer".vendor = "inner".vendor_id)
                    ->  Seq Scan on items i  (cost=0.00..6094564.46 rows=1777 width=1058)
                          Filter: ((add2db > ((('now'::text)::date - '5 days'::interval))::timestamp with time zone) AND (subplan))
                          SubPlan
                            ->  Materialize  (cost=571.46..571.46 rows=51 width=4)
                                  ->  Subquery Scan hlp_items_with_img  (cost=544.45..571.46 rows=51 width=4)
                                        ->  Unique  (cost=544.45..571.46 rows=51 width=4)
                                              ->  Group  (cost=544.45..570.17 rows=514 width=4)
                                                    ->  Sort  (cost=544.45..557.31 rows=5143 width=4)
                                                          Sort Key: img_tovary.item_id
                                                          ->  Seq Scan on img_tovary  (cost=0.00..227.43 rows=5143 width=4)
                    ->  Hash  (cost=7.08..7.08 rows=208 width=824)
                          ->  Seq Scan on vendor v  (cost=0.00..7.08 rows=208 width=824)
              ->  Hash  (cost=1330.37..1330.37 rows=514 width=12)
                    ->  Subquery Scan i  (cost=1291.80..1330.37 rows=514 width=12)
                          ->  Aggregate  (cost=1291.80..1330.37 rows=514 width=12)
                                ->  Group  (cost=1291.80..1317.51 rows=5143 width=12)
                                      ->  Sort  (cost=1291.80..1304.65 rows=5143 width=12)
                                            Sort Key: t.item_id
                                            ->  Hash Join  (cost=631.62..974.77 rows=5143 width=12)
                                                  Hash Cond: ("outer".image_id = "inner".image_id)
                                                  ->  Seq Scan on img_tovary t  (cost=0.00..227.43 rows=5143 width=8)
                                                  ->  Hash  (cost=620.30..620.30 rows=4530 width=4)
                                                        ->  Seq Scan on img_data i  (cost=0.00..620.30 rows=4530 width=4)
        ->  Index Scan using img_data_pkey on img_data i  (cost=0.00..2.01 rows=1 width=32)
              Index Cond: ("outer".image_id = i.image_id)
  ->  Hash  (cost=227.43..227.43 rows=5143 width=4)
        ->  Seq Scan on img_tovary t  (cost=0.00..227.43 rows=5143 width=4)

 

[Sad Spirit]

по следующим данным видно:

• отсутствие запроса;
• отсутствие данных о количестве записей в таблицах;
• отсутствие результатов EXPLAIN ANALYZE.

 

[CrazyHacKeRs]

Делается explain по представлению

CREATE VIEW info_items_new as
--spisok za ukazany interval vremeny
SELECT i.*, l.image_id, l.image_ext FROM info_items i, info_image_list_one l
WHERE (i.item_id=l.item_id) AND (i.item_id IN (SELECT item_id FROM hlp_items_with_img))
AND (i.add2db > (CURRENT_DATE - interval '5 days') );

CREATE VIEW hlp_image_only_one as
SELECT i.item_id, min(image_id) as image_id FROM info_image_list i
GROUP BY item_id;


В таблице items ~ 12 000 записей
В img_data ~ 15 000 записей

А чем Explain отличается от explain analyze?

 

[Sad Spirit]

вот это

i.item_id IN (SELECT item_id FROM hlp_items_with_img)

переделай через JOIN или через EXISTS.

запроса по которому делается explain я всё равно в упор не вижу...

EXPLAIN ANALYZE не только строит план, но и прогоняет запрос: можно посмотреть и оценить, где оптимизатор лажает.

 

[CrazyHacKeRs]

EXPLAIN ANALYZE SELECT * FROM info_items_new;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=9094917.82..9095688.93 rows=107 width=1926) (actual time=727757.24..731695.02 rows=18356 loops=1)
Hash Cond: ("outer".image_id = "inner".image_id)
-> Seq Scan on img_tovary t (cost=0.00..345.65 rows=16965 width=4) (actual time=2.30..98.62 rows=16965 loops=1)
-> Hash (cost=9094917.60..9094917.60 rows=90 width=1922) (actual time=727754.79..727754.79 rows=0 loops=1)
-> Nested Loop (cost=1597.35..9094917.60 rows=90 width=1922) (actual time=2021.24..727671.87 rows=10762 loops=1)
-> Hash Join (cost=1597.35..9094735.75 rows=90 width=1890) (actual time=2021.02..726963.30 rows=10762 loops=1)
Hash Cond: ("outer".item_id = "inner".item_id)
-> Hash Join (cost=7.60..9092895.82 rows=1811 width=1882) (actual time=641.07..725304.72 rows=10765 loops=1)
Hash Cond: ("outer".vendor = "inner".vendor_id)
-> Seq Scan on items i (cost=0.00..9092842.94 rows=1811 width=1058) (actual time=638.68..725057.29 rows=10765 loops=1)
Filter: ((add2db > ((('now'::text)::date - '5 days'::interval))::timestamp with time zone) AND (subplan))
SubPlan
-> Materialize (cost=836.61..836.61 rows=170 width=4) (actual time=0.07..33.75 rows=7342 loops=10868)
-> Subquery Scan hlp_items_with_img (cost=0.00..836.61 rows=170 width=4) (actual time=0.22..544.68 rows=15666 loops=1)
-> Unique (cost=0.00..836.61 rows=170 width=4) (actual time=0.20..403.89 rows=15666 loops=1)
-> Group (cost=0.00..832.37 rows=1696 width=4) (actual time=0.20..262.60 rows=15666 loops=1)
-> Index Scan using img_tovary_idx on img_tovary (cost=0.00..789.96 rows=16965 width=4) (actual time=0.15..111.72 rows=16965 loops=1)
-> Hash (cost=7.08..7.08 rows=208 width=824) (actual time=2.31..2.31 rows=0 loops=1)
-> Seq Scan on vendor v (cost=0.00..7.08 rows=208 width=824) (actual time=0.13..1.28 rows=208 loops=1)
-> Hash (cost=1588.40..1588.40 rows=539 width=12) (actual time=1379.87..1379.87 rows=0 loops=1)
-> Subquery Scan i (cost=1548.01..1588.40 rows=539 width=12) (actual time=647.72..1307.42 rows=15656 loops=1)
-> Aggregate (cost=1548.01..1588.40 rows=539 width=12) (actual time=647.70..1167.92 rows=15656 loops=1)
-> Group (cost=1548.01..1574.94 rows=5386 width=12) (actual time=647.65..948.36 rows=16954 loops=1)
-> Sort (cost=1548.01..1561.47 rows=5386 width=12) (actual time=647.63..727.43 rows=16954 loops=1)
Sort Key: t.item_id
-> Hash Join (cost=631.62..1214.24 rows=5386 width=12) (actual time=165.75..503.96 rows=16954 loops=1)
Hash Cond: ("outer".image_id = "inner".image_id)
-> Seq Scan on img_tovary t (cost=0.00..345.65 rows=16965 width=8) (actual time=2.28..94.20 rows=16965 loops=1)
-> Hash (cost=620.30..620.30 rows=4530 width=4) (actual time=163.38..163.38 rows=0 loops=1)
-> Seq Scan on img_data i (cost=0.00..620.30 rows=4530 width=4) (actual time=7.36..89.04 rows=15849 loops=1)
-> Index Scan using img_data_pkey on img_data i (cost=0.00..2.01 rows=1 width=32) (actual time=0.03..0.03 rows=1 loops=10762)
Index Cond: ("outer".image_id = i.image_id)
Total runtime: 731775.14 msec
(записей: 33)

 

[CrazyHacKeRs]

Заменил как и сказал на JOIN

Теперь:
EXPLAIN ANALYZE SELECT * FROM info_items_new;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=3546.74..4615.66 rows=181 width=1930) (actual time=6748.16..9646.36 rows=18356 loops=1)
Hash Cond: ("outer".image_id = "inner".image_id)
-> Seq Scan on img_tovary t (cost=0.00..345.65 rows=16965 width=4) (actual time=2.20..97.81 rows=16965 loops=1)
-> Hash (cost=3546.36..3546.36 rows=152 width=1926) (actual time=6745.73..6745.73 rows=0 loops=1)
-> Nested Loop (cost=2434.38..3546.36 rows=152 width=1926) (actual time=3900.41..6657.59 rows=10762 loops=1)
-> Hash Join (cost=2434.38..3237.84 rows=152 width=1894) (actual time=3900.25..5653.18 rows=10762 loops=1)
Hash Cond: ("outer".item_id = "inner".item_id)
-> Hash Join (cost=844.63..1640.29 rows=57 width=1886) (actual time=1339.46..2646.94 rows=10765 loops=1)
Hash Cond: ("outer".item_id = "inner".item_id)
-> Hash Join (cost=7.60..639.53 rows=3623 width=1882) (actual time=2.58..896.82 rows=10868 loops=1)
Hash Cond: ("outer".vendor = "inner".vendor_id)
-> Seq Scan on items i (cost=0.00..541.36 rows=3623 width=1058) (actual time=0.26..396.30 rows=10868 loops=1)
Filter: (add2db > ((('now'::text)::date - '5 days'::interval))::timestamp with time zone)
-> Hash (cost=7.08..7.08 rows=208 width=824) (actual time=2.25..2.25 rows=0 loops=1)
-> Seq Scan on vendor v (cost=0.00..7.08 rows=208 width=824) (actual time=0.09..1.24 rows=208 loops=1)
-> Hash (cost=836.61..836.61 rows=170 width=4) (actual time=1336.82..1336.82 rows=0 loops=1)
-> Subquery Scan h (cost=0.00..836.61 rows=170 width=4) (actual time=0.18..1152.62 rows=15666 loops=1)
-> Unique (cost=0.00..836.61 rows=170 width=4) (actual time=0.17..634.20 rows=15666 loops=1)
-> Group (cost=0.00..832.37 rows=1696 width=4) (actual time=0.16..396.97 rows=15666 loops=1)
-> Index Scan using img_tovary_idx on img_tovary (cost=0.00..789.96 rows=16965 width=4) (actual time=0.12..248.39 rows=16965 loops=1)
-> Hash (cost=1588.40..1588.40 rows=539 width=12) (actual time=2560.75..2560.75 rows=0 loops=1)
-> Subquery Scan i (cost=1548.01..1588.40 rows=539 width=12) (actual time=1006.51..2388.30 rows=15656 loops=1)
-> Aggregate (cost=1548.01..1588.40 rows=539 width=12) (actual time=1006.49..2088.68 rows=15656 loops=1)
-> Group (cost=1548.01..1574.94 rows=5386 width=12) (actual time=1006.45..1868.23 rows=16954 loops=1)
-> Sort (cost=1548.01..1561.47 rows=5386 width=12) (actual time=1006.42..1186.25 rows=16954 loops=1)
Sort Key: t.item_id
-> Hash Join (cost=631.62..1214.24 rows=5386 width=12) (actual time=165.31..652.89 rows=16954 loops=1)
Hash Cond: ("outer".image_id = "inner".image_id)
-> Seq Scan on img_tovary t (cost=0.00..345.65 rows=16965 width=8) (actual time=2.33..94.55 rows=16965 loops=1)
-> Hash (cost=620.30..620.30 rows=4530 width=4) (actual time=162.93..162.93 rows=0 loops=1)
-> Seq Scan on img_data i (cost=0.00..620.30 rows=4530 width=4) (actual time=7.40..89.81 rows=15849 loops=1)
-> Index Scan using img_data_pkey on img_data i (cost=0.00..2.01 rows=1 width=32) (actual time=0.04..0.05 rows=1 loops=10762)
Index Cond: ("outer".image_id = i.image_id)
Total runtime: 9726.56 msec


ПОШУСТРЕЕ