CODEWITHSUNDEEP
databaseperformancepostgresqlleft-join
Wykk
Wykk

Reputation: 822

sql query slow 3 leftjoins with where clausel

This first query have one and clausle more and slow down from 200ms to 9-13seconds i dont get why its so.

If i remove all where clause i get ~200ms just if i add one more and it will be slow.

SELECT DISTINCT a.* , p.*, p2.*, p3.*
FROM article a 
LEFT JOIN pro p ON a.id = p.article_id 
LEFT JOIN pro p2 ON a.id = p2.article_id
LEFT JOIN pro p3 ON a.id = p3.article_id
WHERE a.is_active = true 
AND p.name = 'hotel_stars' 
AND p2.name = 'article_journey_days' 
AND p3.name = 'article_persons' 
AND p3.int_value > 0 AND p3.int_value < 7 
AND p.int_value > 0 AND p.int_value < 5

Result

319 Datensätze

Laufzeit gesamt: 9,602.081 ms

SELECT DISTINCT a.* , p.*, p2.*, p3.*
FROM article a 
LEFT JOIN property p ON a.id = p.article_id 
LEFT JOIN property p2 ON a.id = p2.article_id
LEFT JOIN property p3 ON a.id = p3.article_id
WHERE a.is_active = true 
AND p.name = 'hotel_stars' 
AND p2.name = 'article_property_journey_days' 
AND p3.name = 'article_property_persons' 
AND p3.int_value > 0 AND p3.int_value < 7 
// AND p.int_value > 0 AND p.int_value < 5 (removed)

Result

469 Datensätze

Laufzeit gesamt: 278.453 ms

Where is the Problem? Thx

EDIT EXPLAIN PLAN: 

HashAggregate  (cost=24113.80..24113.81 rows=1 width=3528)
  ->  Nested Loop  (cost=0.00..24113.69 rows=1 width=3528)
        Join Filter: (a.id = p2.article_id)
        ->  Nested Loop  (cost=0.00..16889.70 rows=1 width=2488)
              ->  Nested Loop  (cost=0.00..16856.58 rows=4 width=2080)
                    Join Filter: (p.article_id = p3.article_id)
                    ->  Seq Scan on property p  (cost=0.00..8335.87 rows=115 width=1040)
                          Filter: ((int_value > 0) AND (int_value < 5) AND ((name)::text = 'hotel_stars'::text))
                    ->  Materialize  (cost=0.00..8336.41 rows=107 width=1040)
                          ->  Seq Scan on property p3  (cost=0.00..8335.87 rows=107 width=1040)
                                Filter: ((int_value > 0) AND (int_value < 7) AND ((name)::text = 'article_property_persons'::text))
              ->  Index Scan using article_pkey on article a  (cost=0.00..8.27 rows=1 width=408)
                    Index Cond: (id = p.article_id)
                    Filter: is_active
        ->  Seq Scan on property p2  (cost=0.00..7185.05 rows=3115 width=1040)
              Filter: ((name)::text = 'article_property_journey_days'::text)
16 Datensätze

Laufzeit gesamt: 11.153 ms

Changing To 

SELECT DISTINCT a.* , p.*, p2.*, p3.*
    FROM article a 
    INNER JOIN pro p ON a.id = p.article_id AND p.name = 'hotel_stars' AND p.int_value > 0 AND p.int_value < 5
    INNER JOIN pro p2 ON a.id = p2.article_id AND p2.name = 'article_journey_days' 
    INNER JOIN pro p3 ON a.id = p3.article_id  AND p3.name = 'article_persons' AND p3.int_value > 0 AND p3.int_value < 7 
    WHERE a.is_active = true

Result:

319 Datensätze

Laufzeit gesamt: 9,315.863 ms

HashAggregate  (cost=24113.80..24113.81 rows=1 width=3528)
  ->  Nested Loop  (cost=0.00..24113.69 rows=1 width=3528)
        Join Filter: (a.id = p2.article_id)
        ->  Nested Loop  (cost=0.00..16889.70 rows=1 width=2488)
              ->  Nested Loop  (cost=0.00..16856.58 rows=4 width=2080)
                    Join Filter: (p.article_id = p3.article_id)
                    ->  Seq Scan on property p  (cost=0.00..8335.87 rows=115 width=1040)
                          Filter: ((int_value > 0) AND (int_value < 5) AND ((name)::text = 'hotel_stars'::text))
                    ->  Materialize  (cost=0.00..8336.41 rows=107 width=1040)
                          ->  Seq Scan on property p3  (cost=0.00..8335.87 rows=107 width=1040)
                                Filter: ((int_value > 0) AND (int_value < 7) AND ((name)::text = 'article_property_persons'::text))
              ->  Index Scan using article_pkey on article a  (cost=0.00..8.27 rows=1 width=408)
                    Index Cond: (id = p.article_id)
                    Filter: is_active
        ->  Seq Scan on property p2  (cost=0.00..7185.05 rows=3115 width=1040)
              Filter: ((name)::text = 'article_property_journey_days'::text)
16 Datensätze

Laufzeit gesamt: 4.314 ms

Similar question :(

Upvotes: 1

Views: 58

Answers (1)

Wykk
Wykk

Reputation: 822

Added Index for column p.name and p.article. Result speed improve from 13sec to 180ms.

Upvotes: 1

Related Questions