MxBlog

Impact of inline views on query plan

After last blog post, researching the effect of duplicate where clauses on performance, i thought of another case to try. How do inline views impact the ability of postgres to use indexes defined on tables?

The following query is similar to the last few queries of my last post.

explain analyze
  select * 
  from   ( select *
           from   t1
         ) as st1
  ,      ( select *
           from   t2
         ) as st2
  ,      ( select *
           from   t3
           where  t3.n1 = 10
         ) as st3
  where  st1.n1 = st2.n1
  and    st1.n2 = st2.n2
  and    st2.n1 = st3.n1
  and    st2.n2 = st3.n2
  and    st1.n1 = 10
  and    st1.n2 = 100;

The query plan for this query is as follows:

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..25.16 rows=1 width=270) (actual time=11.686..11.758 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..16.77 rows=1 width=180) (actual time=10.378..10.416 rows=1 loops=1)
         ->  Index Scan using t1_n1_n2_idx on t1  (cost=0.00..8.38 rows=1 width=90) (actual time=8.799..8.807 rows=1 loops=1)
               Index Cond: ((n1 = 10) AND (n2 = 100))
         ->  Index Scan using t2_n1_n2_idx on t2  (cost=0.00..8.38 rows=1 width=90) (actual time=1.493..1.502 rows=1 loops=1)
               Index Cond: ((n1 = 10) AND (n2 = 100))
   ->  Index Scan using t3_n1_n2_idx on t3  (cost=0.00..8.38 rows=1 width=90) (actual time=1.271..1.283 rows=1 loops=1)
         Index Cond: ((n1 = 10) AND (n2 = 100))
 Total runtime: 12.038 ms
(9 rows)

As you can see, postgres is using the correct indexes on the inline views. Even the extra condition on the third inline view isn't causing postgres to pick the wrong index.

PgAdmin Query Plan

Btw, pgadmin has a visual query plan tool. The pgadmin tool shows the following query plan: