Search Postgresql Archives

optimizing a query with sub select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,
I have this query
 SELECT
  *
FROM
  v_material
WHERE
    show_in_recent AND section_id IN (
      SELECT
        s.id
      FROM
        section AS s, section AS s2
      WHERE
        s2.id = 842
        AND
        s.breadcrumb <@ s2.breadcrumb
        )
ORDER BY
  published_on DESC
LIMIT 3;

This query is slow (2 sec)

if i just remove the sub-select in IN clause, and replace it with the  value of the sub-select ,the query is very fast.
The sub-select itself is very fast.
Is there a way to optimize it ?

Here are the 2 execution plans
Slow :
                                                                                                       QUERY PLAN                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=82.13..92.65 rows=3 width=1109) (actual time=2626.954..2626.954 rows=0 loops=1)
   ->  Nested Loop Left Join  (cost=82.13..633409.54 rows=180475 width=1109) (actual time=2626.952..2626.952 rows=0 loops=1)
         ->  Nested Loop Semi Join  (cost=82.13..568691.75 rows=180475 width=1105) (actual time=2626.951..2626.951 rows=0 loops=1)
               Join Filter: (s.id = s.id)
               ->  Nested Loop  (cost=0.00..564548.94 rows=180475 width=1109) (actual time=0.096..2536.080 rows=179994 loops=1)
                     ->  Nested Loop  (cost=0.00..511665.26 rows=180475 width=1106) (actual time=0.092..2236.543 rows=179994 loops=1)
                           ->  Nested Loop Left Join  (cost=0.00..460819.28 rows=180475 width=1001) (actual time=0.084..1789.881 rows=179994 loops=1)
                                 ->  Nested Loop  (cost=0.00..403069.44 rows=180475 width=997) (actual time=0.067..1442.077 rows=179994 loops=1)
                                       ->  Nested Loop  (cost=0.00..352510.00 rows=180475 width=989) (actual time=0.061..1161.405 rows=179994 loops=1)
                                             ->  Nested Loop Left Join  (cost=0.00..301950.56 rows=180475 width=975) (actual time=0.056..873.697 rows=179994 loops=1)
                                                   ->  Nested Loop  (cost=0.00..237987.06 rows=180475 width=971) (actual time=0.046..707.396 rows=179994 loops=1)
                                                         ->  Index Scan Backward using material_published_on on material m  (cost=0.00..187427.62 rows=180475 width=956) (actual time=0.037..392.091 rows=179994 loops=1)
                                                               Filter: (active AND show_in_recent)
                                                         ->  Index Scan using staff_pkey on staff s  (cost=0.00..0.27 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=179994)
                                                               Index Cond: (s.id = m.created_by)
                                                   ->  Index Scan using rating_pkey on rating r  (cost=0.00..0.34 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=179994)
                                                         Index Cond: (m.rating_id = r.id)
                                             ->  Index Scan using lookup_material_type_pkey on lookup_material_type mt  (cost=0.00..0.27 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=179994)
                                                   Index Cond: (mt.id = m.type_id)
                                       ->  Index Scan using lookup_material_importance_pkey on lookup_material_importance lmi  (cost=0.00..0.27 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=179994)
                                             Index Cond: (lmi.id = m.importance_id)
                                 ->  Index Scan using material_images_pkey on material_images mi  (cost=0.00..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=179994)
                                       Index Cond: (m.id = mi.material_id)
                                       Filter: (mi."position" = 1)
                           ->  Index Scan using section_pkey on section s  (cost=0.00..0.27 rows=1 width=105) (actual time=0.001..0.002 rows=1 loops=179994)
                                 Index Cond: (s.id = m.section_id)
                     ->  Index Scan using lookup_section_type_pkey on lookup_section_type lst  (cost=0.00..0.28 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=179994)
                           Index Cond: (lst.id = s.type_id)
               ->  Materialize  (cost=82.13..82.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=179994)
                     ->  Nested Loop  (cost=0.00..82.13 rows=1 width=4) (actual time=0.535..0.730 rows=1 loops=1)
                           Join Filter: (s.breadcrumb <@ s2.breadcrumb)
                           ->  Index Scan using section_pkey on section s2  (cost=0.00..8.27 rows=1 width=34) (actual time=0.005..0.006 rows=1 loops=1)
                                 Index Cond: (id = 842)
                           ->  Seq Scan on section s  (cost=0.00..62.27 rows=927 width=38) (actual time=0.003..0.248 rows=927 loops=1)
         ->  Index Scan using forum_pkey on forum f  (cost=0.00..0.34 rows=1 width=8) (never executed)
               Index Cond: (m.forum_id = f.id)

 Total runtime: 2627.290 ms
===============================================
Also what is the meaning of MATERIALIZE in explain analyze ?

Fast ;
SELECT
  *
FROM
  v_material
WHERE
    show_in_recent AND section_id IN (
      842
     
        )
ORDER BY
  published_on DESC
LIMIT 3;

Limit  (cost=3458.91..3458.92 rows=3 width=1109) (actual time=0.189..0.189 rows=0 loops=1)
   ->  Sort  (cost=3458.91..3459.24 rows=133 width=1109) (actual time=0.189..0.189 rows=0 loops=1)
         Sort Key: m.published_on
         Sort Method:  quicksort  Memory: 25kB
         ->  Nested Loop Left Join  (cost=18.02..3457.19 rows=133 width=1109) (actual time=0.184..0.184 rows=0 loops=1)
               ->  Nested Loop Left Join  (cost=18.02..2419.92 rows=133 width=1105) (actual time=0.184..0.184 rows=0 loops=1)
                     ->  Hash Join  (cost=18.02..1590.87 rows=111 width=1101) (actual time=0.184..0.184 rows=0 loops=1)
                           Hash Cond: (m.importance_id = lmi.id)
                           ->  Hash Join  (cost=16.93..1588.25 rows=111 width=1093) (actual time=0.173..0.173 rows=0 loops=1)
                                 Hash Cond: (m.type_id = mt.id)
                                 ->  Hash Join  (cost=15.66..1585.46 rows=111 width=1079) (actual time=0.163..0.163 rows=0 loops=1)
                                       Hash Cond: (m.created_by = s.id)
                                       ->  Nested Loop  (cost=13.74..1582.01 rows=111 width=1064) (actual time=0.137..0.137 rows=0 loops=1)
                                             ->  Nested Loop  (cost=0.00..9.43 rows=1 width=108) (actual time=0.011..0.013 rows=1 loops=1)
                                                   Join Filter: (s.type_id = lst.id)
                                                   ->  Index Scan using section_pkey on section s  (cost=0.00..8.27 rows=1 width=105) (actual time=0.006..0.006 rows=1 loops=1)
                                                         Index Cond: (id = 842)
                                                   ->  Seq Scan on lookup_section_type lst  (cost=0.00..1.07 rows=7 width=11) (actual time=0.000..0.001 rows=7 loops=1)
                                             ->  Nested Loop Left Join  (cost=13.74..1571.47 rows=111 width=960) (actual time=0.122..0.122 rows=0 loops=1)
                                                   ->  Bitmap Heap Scan on material m  (cost=13.74..693.56 rows=111 width=956) (actual time=0.122..0.122 rows=0 loops=1)
                                                         Recheck Cond: (section_id = 842)
                                                         Filter: (active AND show_in_recent)
                                                         ->  Bitmap Index Scan on section_total_visits  (cost=0.00..13.71 rows=177 width=0) (actual time=0.034..0.034 rows=170 loops=1)
                                                               Index Cond: (section_id = 842)
                                                   ->  Index Scan using rating_pkey on rating r  (cost=0.00..7.90 rows=1 width=8) (never executed)
                                                         Index Cond: (m.rating_id = r.id)
                                       ->  Hash  (cost=1.41..1.41 rows=41 width=19) (actual time=0.022..0.022 rows=41 loops=1)
                                             ->  Seq Scan on staff s  (cost=0.00..1.41 rows=41 width=19) (actual time=0.003..0.012 rows=41 loops=1)
                                 ->  Hash  (cost=1.12..1.12 rows=12 width=18) (actual time=0.007..0.007 rows=12 loops=1)
                                       ->  Seq Scan on lookup_material_type mt  (cost=0.00..1.12 rows=12 width=18) (actual time=0.001..0.004 rows=12 loops=1)
                           ->  Hash  (cost=1.04..1.04 rows=4 width=12) (actual time=0.007..0.007 rows=4 loops=1)
                                 ->  Seq Scan on lookup_material_importance lmi  (cost=0.00..1.04 rows=4 width=12) (actual time=0.003..0.003 rows=4 loops=1)
                     ->  Index Scan using material_images_pkey on material_images mi  (cost=0.00..7.46 rows=1 width=8) (never executed)
                           Index Cond: (m.id = mi.material_id)
                           Filter: (mi."position" = 1)
               ->  Index Scan using forum_pkey on forum f  (cost=0.00..7.78 rows=1 width=8) (never executed)
                     Index Cond: (m.forum_id = f.id)
 Total runtime: 0.402 ms


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux