Re: Merge Joins and Views - Mailing list pgsql-general

See attached -- I've simplified my actual database quite a bit, but this
example shows the same results.

Thanks,
--Chris
                             version
------------------------------------------------------------------
 PostgreSQL 8.3.0 on sparc-sun-solaris2.8, compiled by GCC 2.95.2
(1 row)

DROP VIEW
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE VIEW
INSERT 0 5000000
INSERT 0 3711523
ANALYZE
ANALYZE
 schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs |
                                                                                    histogram_bounds
                                                                         | correlation  

------------+-----------+---------+-----------+-----------+------------+------------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
 public     | a         | id      |         0 |         4 |         -1 |                  |                   |
{226,480817,946403,1463901,1905168,2486162,2964834,3411486,3947522,4446167,4996780}
                                                                                 |           1 
 public     | a         | val     |         0 |         8 |         -1 |                  |                   |
{0.00023875804618001,0.0914572249166667,0.189253146760166,0.282982839271426,0.393971057608724,0.491479988675565,0.592469296883792,0.693580291699618,0.803486418910325,0.899317930918187,0.999949590768665}
| -0.0345742 
 public     | b         | id      |         0 |         4 |         -1 |                  |                   |
{2380,409226,804058,1186283,1525765,1874817,2199262,2566896,2939230,3316455,3709638}
                                                                                 |           1 
 public     | b         | opt     |  0.503667 |         8 |         -1 |                  |                   |
{0.000438648741692305,0.0946335387416184,0.194745551329106,0.308890894055367,0.403113955631852,0.50895657017827,0.62006954383105,0.724281970411539,0.805469979997724,0.907830006908625,0.999940330628306}
|    0.034033 
(4 rows)

                                                              QUERY PLAN
              

--------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=0.00..330371.44 rows=5000180 width=24) (actual time=0.319..30850.276 rows=5000000 loops=1)
   Merge Cond: (a.id = b.id)
   ->  Index Scan using a_pkey on a  (cost=0.00..156882.50 rows=5000180 width=12) (actual time=0.244..12665.648
rows=5000000loops=1) 
   ->  Index Scan using b_pkey on b  (cost=0.00..114600.84 rows=3711012 width=12) (actual time=0.061..7336.846
rows=3711523loops=1) 
 Total runtime: 32191.735 ms
(5 rows)

                                                              QUERY PLAN
              

--------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=560793.89..785842.02 rows=5000180 width=24) (actual time=23542.157..55124.203 rows=5000000
loops=1)
   Merge Cond: (a.id = b.id)
   ->  Index Scan using a_pkey on a  (cost=0.00..156882.50 rows=5000180 width=12) (actual time=0.282..12397.933
rows=5000000loops=1) 
   ->  Materialize  (cost=560793.89..607181.54 rows=3711012 width=12) (actual time=23541.845..31825.216 rows=3711523
loops=1)
         ->  Sort  (cost=560793.89..570071.42 rows=3711012 width=12) (actual time=23541.833..28215.551 rows=3711523
loops=1)
               Sort Key: b.id
               Sort Method:  external sort  Disk: 116056kB
               ->  Seq Scan on b  (cost=0.00..55326.12 rows=3711012 width=12) (actual time=0.073..4694.892 rows=3711523
loops=1)
 Total runtime: 56409.946 ms
(9 rows)

                                                             QUERY PLAN
             

-------------------------------------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=0.00..367481.56 rows=5000180 width=24) (actual time=1.794..28075.029 rows=5000000 loops=1)
   Merge Cond: (b.id = a.id)
   ->  Index Scan using b_pkey on b  (cost=0.00..114600.84 rows=3711012 width=12) (actual time=0.322..8132.872
rows=3711523loops=1) 
   ->  Index Scan using a_pkey on a  (cost=0.00..156882.50 rows=5000180 width=12) (actual time=1.457..9714.089
rows=5000000loops=1) 
 Total runtime: 29366.349 ms
(5 rows)