================================================== ==========================
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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

SHADOWPLAY - Dave Adams <jugular (AT) umbra (DOT) shadowplay.net> writes:
PG 7.3 is not very bright about pushing WHERE quals down into a
sub-select that involves DISTINCT ON. 7.4 is a tad smarter, and I think
it will handle your example nicely --- though there are certainly plenty
of similar-looking cases that are just plain not optimizable. You might
find it interesting to compare the comments in the 7.3 and 7.4 versions
of
http://developer.postgresql.org/cvsw...ath/allpaths.c
in particular the 1.99-1.100 diff.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly