title: why does my view in postgresql not use the index?
body: <p>I have a large table (star catalog), of which I have a subset. I implement the subset
  as a union of two tables, where I make use of a cross index.</p>
  
  <p>The issue is that a query from the view doesn't seem to be using the index, the time takes the same as a scan through the table.</p>
  
  <p>A query against the large table goes quickly:</p>
  
  <pre><code>select count(*) from ucac4 where rnm in (select ucac4_rnm from grid_catalog limit 5);
  count 
  -------
       5
  (1 row)
  
  Time: 12.132 ms
  </code></pre>
  
  <p>A query against the view does not go quickly, even though I would expect it to.</p>
  
  <pre><code>select count(*) from grid_catalog_view where ident in (select ucac4_rnm from grid_catalog limit 5);
  count 
  -------
       5
  (1 row)
  
  Time: 1056237.045 ms
  </code></pre>
  
  <p>An explain of this query yeilds:</p>
  
  <pre><code> Aggregate  (cost=23175810.51..23175810.52 rows=1 width=0)
     -&gt;  Hash Join  (cost=23081888.41..23172893.67 rows=1166734 width=0)
           Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm)
           -&gt;  Unique  (cost=23081888.17..23140224.87 rows=2333468 width=44)
                 -&gt;  Sort  (cost=23081888.17..23087721.84 rows=2333468 width=44)
                       Sort Key: ucac4.ra, ucac4."dec", ucac4.pmrac, ucac4.pmdc, ucac4.rnm, ucac4.nest4, ucac4.nest6, ucac4.nest7, public.grid_catalog.subset
                       -&gt;  Append  (cost=63349.87..22763295.24 rows=2333468 width=44)
                             -&gt;  Hash Join  (cost=63349.87..22738772.75 rows=2333467 width=44)
                                   Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm)
                                   -&gt;  Seq Scan on ucac4  (cost=0.00..16394129.04 rows=455124304 width=40)
                                   -&gt;  Hash  (cost=34048.69..34048.69 rows=2344094 width=8)
                                         -&gt;  Seq Scan on grid_catalog  (cost=0.00..34048.69 rows=2344094 width=8)
                                               Filter: (petrov_prikey IS NULL)
                             -&gt;  Hash Join  (cost=415.51..1187.80 rows=1 width=36)
                                   Hash Cond: (petrov.prikey = public.grid_catalog.petrov_prikey)
                                   -&gt;  Seq Scan on petrov  (cost=0.00..709.15 rows=7215 width=32)
                                   -&gt;  Hash  (cost=282.08..282.08 rows=10675 width=8)
                                         -&gt;  Index Scan using grid_catalog_petrov_prikey_idx on grid_catalog  (cost=0.00..282.08 row
  s=10675 width=8)
           -&gt;  Hash  (cost=0.18..0.18 rows=5 width=4)
                 -&gt;  HashAggregate  (cost=0.13..0.18 rows=5 width=4)
                       -&gt;  Limit  (cost=0.00..0.07 rows=5 width=4)
                             -&gt;  Seq Scan on grid_catalog  (cost=0.00..34048.69 rows=2354769 width=4)
  (22 rows)
  </code></pre>
  
  <p>The explain analyze (request in a comment) is:</p>
  
  <pre><code> Aggregate  (cost=23175810.51..23175810.52 rows=1 width=0) (actual time=1625067.627..1625067.628 rows=1 loops=1)
     -&gt;  Hash Join  (cost=23081888.41..23172893.67 rows=1166734 width=0) (actual time=1621395.200..1625067.618 rows=5 loops=1)
           Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm)
           -&gt;  Unique  (cost=23081888.17..23140224.87 rows=2333468 width=44) (actual time=1620897.932..1624102.849 rows=1597359 loops
  =1)
                 -&gt;  Sort  (cost=23081888.17..23087721.84 rows=2333468 width=44) (actual time=1620897.928..1622191.358 rows=1597359 l
  oops=1)
                       Sort Key: ucac4.ra, ucac4."dec", ucac4.pmrac, ucac4.pmdc, ucac4.rnm, ucac4.nest4, ucac4.nest6, ucac4.nest7, pu
  blic.grid_catalog.subset
                       Sort Method:  external merge  Disk: 87536kB
                       -&gt;  Append  (cost=63349.87..22763295.24 rows=2333468 width=44) (actual time=890293.619..1613769.160 rows=15973
  59 loops=1)
                             -&gt;  Hash Join  (cost=63349.87..22738772.75 rows=2333467 width=44) (actual time=890293.617..1611550.313 r
  ows=1590144 loops=1)
                                   Hash Cond: (ucac4.rnm = public.grid_catalog.ucac4_rnm)
                                   -&gt;  Seq Scan on ucac4  (cost=0.00..16394129.04 rows=455124304 width=40) (actual time=886086.630..1
  359934.589 rows=113780093 loops=1)
                                   -&gt;  Hash  (cost=34048.69..34048.69 rows=2344094 width=8) (actual time=4203.785..4203.785 rows=1590
  144 loops=1)
                                         -&gt;  Seq Scan on grid_catalog  (cost=0.00..34048.69 rows=2344094 width=8) (actual time=0.014.
  .2813.031 rows=1590144 loops=1)
                                               Filter: (petrov_prikey IS NULL)
                             -&gt;  Hash Join  (cost=415.51..1187.80 rows=1 width=36) (actual time=101.604..165.749 rows=7215 loops=1)
                                   Hash Cond: (petrov.prikey = public.grid_catalog.petrov_prikey)
                                   -&gt;  Seq Scan on petrov  (cost=0.00..709.15 rows=7215 width=32) (actual time=58.280..108.043 rows=7
  215 loops=1)
                                   -&gt;  Hash  (cost=282.08..282.08 rows=10675 width=8) (actual time=43.276..43.276 rows=7215 loops=1)
                                         -&gt;  Index Scan using grid_catalog_petrov_prikey_idx on grid_catalog  (cost=0.00..282.08 rows
  =10675 width=8) (actual time=19.387..37.533 rows=7215 loops=1)
           -&gt;  Hash  (cost=0.18..0.18 rows=5 width=4) (actual time=0.035..0.035 rows=5 loops=1)
                 -&gt;  HashAggregate  (cost=0.13..0.18 rows=5 width=4) (actual time=0.026..0.030 rows=5 loops=1)
                       -&gt;  Limit  (cost=0.00..0.07 rows=5 width=4) (actual time=0.009..0.017 rows=5 loops=1)
                             -&gt;  Seq Scan on grid_catalog  (cost=0.00..34048.69 rows=2354769 width=4) (actual time=0.007..0.009 rows=
  5 loops=1)
   Total runtime: 1625108.504 ms
  (24 rows)
  
  Time: 1625466.830 ms
  </code></pre>
  
  <p>To see the time to scan through the view:</p>
  
  <pre><code>select count(*) from grid_catalog_view;
    count  
  ---------
   1597359
  (1 row)
  
  Time: 1033732.786 ms
  </code></pre>
  
  <p>My view is defined as:</p>
  
  <pre><code>PS1=# \d grid_catalog_view
      View "public.grid_catalog_view"
   Column |       Type       | Modifiers 
  --------+------------------+-----------
   ra     | double precision | 
   dec    | double precision | 
   pmrac  | integer          | 
   pmdc   | integer          | 
   ident  | integer          | 
   nest4  | integer          | 
   nest6  | integer          | 
   nest7  | integer          | 
   subset | integer          | 
  View definition:
           SELECT ucac4.ra, ucac4."dec", ucac4.pmrac, ucac4.pmdc, ucac4.rnm AS ident, ucac4.nest4, ucac4.nest6, ucac4.nest7, grid_catalog.subset
             FROM ucac4, grid_catalog
            WHERE ucac4.rnm = grid_catalog.ucac4_rnm AND grid_catalog.petrov_prikey IS NULL
  UNION 
           SELECT petrov.ra, petrov."dec", 0 AS pmrac, 0 AS pmdc, grid_catalog.petrov_prikey AS ident, petrov.nest4, petrov.nest6, petrov.nest7, grid_catalog.subset
             FROM petrov, grid_catalog
            WHERE petrov.prikey = grid_catalog.petrov_prikey AND grid_catalog.ucac4_rnm IS NULL;
  </code></pre>
  
  <p>The large table is defined as:</p>
  
  <pre><code>PS1=# \d ucac4
            Table "public.ucac4"
    Column  |       Type       | Modifiers 
  ----------+------------------+-----------
   radi     | bigint           | 
   spdi     | bigint           | 
   magm     | smallint         | 
   maga     | smallint         | 
   sigmag   | smallint         | 
   objt     | smallint         | 
   cdf      | smallint         |
  ... deleted entries not of relavance ...
   ra       | double precision | 
   dec      | double precision | 
   x        | double precision | 
   y        | double precision | 
   z        | double precision | 
   nest4    | integer          | 
   nest6    | integer          | 
   nest7    | integer          | 
  Indexes:
      "ucac4_pkey" PRIMARY KEY, btree (rnm)
      "q3c_ucac4_idx" btree (q3c_ang2ipix(ra, "dec")) CLUSTER
      "ucac4_nest4_idx" btree (nest4)
      "ucac4_nest6_idx" btree (nest6)
      "ucac4_nest7_idx" btree (nest7)
  Referenced by:
      TABLE "grid_catalog" CONSTRAINT "grid_catalog_ucac4_rnm_fkey" FOREIGN KEY (ucac4_rnm) REFERENCES ucac4(rnm)
  </code></pre>
  
  <p>Any idea why my index doesn't seem to be used?</p>