Thread: Selecting from a VIEW is NOT optimized like a similar SELECT statement
Selecting from a VIEW is NOT optimized like a similar SELECT statement
From
SHADOWPLAY - Dave Adams
Date:
============================================================================
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
SHADOWPLAY - Dave Adams <jugular@umbra.shadowplay.net> writes: > When using a view on a table, a select on the view is not as optimized as > a select directly from the table. 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/cvsweb.cgi/pgsql-server/src/backend/optimizer/path/allpaths.c in particular the 1.99-1.100 diff. regards, tom lane