Selecting from a VIEW is NOT optimized like a similar SELECT statement - Mailing list pgsql-bugs

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               :       David B. Adams
Your email address      :       jugular ( at ) shadowplay ( dot ) net


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : AMD Athlon XP

  Operating System (example: Linux 2.0.26 ELF)  : Linux  2.4.20-20.9
(RedHat-9)

  PostgreSQL version (example: PostgreSQL-7.3):   PostgreSQL-7.3.2-3

  Compiler used (example:  gcc 2.95.2)          : (Redhat package)


Please enter a FULL description of your problem:
------------------------------------------------

When using a view on a table, a select on the view is not as optimized as
a select directly from the table.

1)
drop view dl_entry_view;
create view dl_entry_view as  SELECT DISTINCT ON (dl_entry.dl_no,
dl_entry.entry_name, dl_entry.entry_npi, dl_entry.entry_ton)
dl_entry.dl_no, dl_entry.entry_name, dl_entry.entry_npi,
dl_entry.entry_ton, dl_entry.entry_desc, dl_entry.entry_type FROM dl_entry
ORDER BY dl_entry.dl_no, dl_entry.entry_name, dl_entry.entry_npi,
dl_entry.entry_ton;
explain select * from dl_entry_view where dl_no = 33;

2)
drop view dl_entry_view;
create view dl_entry_view as  SELECT DISTINCT ON (dl_entry.dl_no,
dl_entry.entry_name, dl_entry.entry_npi, dl_entry.entry_ton)
dl_entry.dl_no, dl_entry.entry_name, dl_entry.entry_npi,
dl_entry.entry_ton, dl_entry.entry_desc, dl_entry.entry_type FROM
dl_entry;
explain select * from dl_entry_view where dl_no = 33 order by 1,2,3,4;

3)
explain select  DISTINCT ON (dl_entry.dl_no, dl_entry.entry_name,
dl_entry.entry_npi, dl_entry.entry_ton)  dl_entry.dl_no,
dl_entry.entry_name, dl_entry.entry_npi, dl_entry.entry_ton,
dl_entry.entry_desc, dl_entry.entry_type FROM dl_entry where dl_no = 33
ORDER BY dl_entry.dl_no, dl_entry.entry_name, dl_entry.entry_npi,
dl_entry.entry_ton;

4)
explain select  DISTINCT ON (dl_no, entry_name, entry_npi, entry_ton) *
FROM dl_entry where dl_no = 33 ORDER BY dl_entry.dl_no,
dl_entry.entry_name, dl_entry.entry_npi, dl_entry.entry_ton;

5)
explain select  DISTINCT ON (dl_no, entry_name, entry_npi, entry_ton) *
FROM dl_entry where dl_no = 33 ORDER BY 1,2,3,4;

Using the following table:
            Table "public.dl_entry"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 dl_no      | integer               | not null
 entry_name | character varying(21) | not null
 entry_npi  | integer               | not null
 entry_ton  | integer               | not null
 entry_desc | character varying(21) |
 entry_type | integer               | not null
Indexes: dl_entry_ndx2 unique btree (dl_no, entry_desc),
         dl_entry_ndx btree (dl_no, entry_name, entry_npi, entry_ton)

The result for the top 5 queries in the explain plan are as follows:
1)
                                             QUERY PLAN

-----------------------------------------------------------------------------------------------------
 Subquery Scan dl_entry_view  (cost=110974.37..117361.87 rows=51100
width=66)
   Filter: (dl_no = 33)
   ->  Unique  (cost=110974.37..117361.87 rows=51100 width=66)
         ->  Sort  (cost=110974.37..112251.87 rows=511000 width=66)
               Sort Key: dl_entry.dl_no, dl_entry.entry_name,
dl_entry.entry_npi, dl_entry.entry_ton
               ->  Seq Scan on dl_entry  (cost=0.00..9134.00 rows=511000
width=66)
(6 rows)

2)
                                                QUERY PLAN

-----------------------------------------------------------------------------------------------------------
 Sort  (cost=122695.28..122823.03 rows=51100 width=66)
   Sort Key: dl_no, entry_name, entry_npi, entry_ton
   ->  Subquery Scan dl_entry_view  (cost=110974.37..117361.87 rows=51100
width=66)
         Filter: (dl_no = 33)
         ->  Unique  (cost=110974.37..117361.87 rows=51100 width=66)
               ->  Sort  (cost=110974.37..112251.87 rows=511000 width=66)
                     Sort Key: dl_entry.dl_no, dl_entry.entry_name,
dl_entry.entry_npi, dl_entry.entry_ton
                     ->  Seq Scan on dl_entry  (cost=0.00..9134.00
rows=511000 width=66)
(8 rows)

3)
                                        QUERY PLAN

------------------------------------------------------------------------------------------
 Unique  (cost=0.00..8323.82 rows=256 width=66)
   ->  Index Scan using dl_entry_ndx on dl_entry  (cost=0.00..8298.27
rows=2555
width=66)
         Index Cond: (dl_no = 33)
(3 rows)

4)
                                        QUERY PLAN

------------------------------------------------------------------------------------------
 Unique  (cost=0.00..8323.82 rows=256 width=66)
   ->  Index Scan using dl_entry_ndx on dl_entry  (cost=0.00..8298.27
rows=2555
width=66)
         Index Cond: (dl_no = 33)
(3 rows)

5)
                                        QUERY PLAN

------------------------------------------------------------------------------------------
 Unique  (cost=0.00..8323.82 rows=256 width=66)
   ->  Index Scan using dl_entry_ndx on dl_entry  (cost=0.00..8298.27
rows=2555
width=66)
         Index Cond: (dl_no = 33)
(3 rows)


====================
As seen above, the queries are performing the exact same select on the
table, but the explain results, as well as the actual results, come out
very different.

The reason for this coming up was to use a simple view to remove duplicate
values (see dl_entry_ndx index) from a table for viewing.

Is there any special indexing that can be performed, or is the a problem
when the final query is executed ?

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

see above.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

NONE


Thanks
David Adams