[BUGS] Selecting from a VIEW is NOT optimized like a similar SELECT statement

[SHADOWPLAY - Dave Adams]

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_n

[BUGS] RC1 question of reloading data

[Theodore Petrosky]

I just downloaded and installed RC1 (7.4) on OSX 10.3.
Everything went smoothly for the config. make and
install. 

while reloading the data (from pg_dump in 7.3) I
inadvertently screwed up. Or maybe not...

my dump file was as1.out so I...

psql < as1.out

I knew it was wrong the moment after I hit the return
as I did not specify the database (i did create it
with createdb). I did not get any error message. I
went looking to see if the data was pushed somewhere
bad like template1. But couldn't find it.

I did load my data correctly but, where did the first
set go? Did it just disappear into the ether?

Ted Petrosky

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [BUGS] Selecting from a VIEW is NOT optimized like a similar SELECT statement

[Tom Lane]

SHADOWPLAY - Dave Adams <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly