Dmitriy A. Anipko (anipko@tornado.nsk.ru) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Big problems with query optimization
Long Description
I use a 7.0 release running under SunOS on SPARC.
I found that there is no real way to perform queries to several tables if these tables contain quite a big amount of
data.
Example code shows it.
There are situations when even obvious optimizations are not performed.
Important: there is no such a bug in 6.5.* releases (running under Linux on i686).
Sample Code
Release 7.0
faqts=> explain select faqs.id from faqs;
NOTICE: QUERY PLAN:
Seq Scan on faqs (cost=0.00..2.40 rows=40 width=4)
EXPLAIN
faqts=> explain select faqs.id from faqs, faq_keywords;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..4908.80 rows=161320 width=8)
-> Seq Scan on faqs (cost=0.00..2.40 rows=40 width=4)
-> Seq Scan on faq_keywords (cost=0.00..82.33 rows=4033 width=4)
EXPLAIN
faqts=> explain select distinct on (faqs.id) faqs.id from faqs, faq_keywords;
NOTICE: QUERY PLAN:
Unique (cost=0.00..5319.14 rows=16132 width=8)
-> Nested Loop (cost=0.00..4915.84 rows=161320 width=8)
-> Index Scan using faqs_pkey on faqs (cost=0.00..9.44 rows=40 width=4)
-> Seq Scan on faq_keywords (cost=0.00..82.33 rows=4033 width=4)
EXPLAIN
faqts=>
Compare with release 6.5 reaction:
faqtat=> explain select faqs.id from faqs, faq_keywords;
NOTICE: QUERY PLAN:
Seq Scan on faqs (cost=43.00 rows=1000 width=4)
EXPLAIN
faqtat=> explain select faqs.id from faqs;
NOTICE: QUERY PLAN:
Seq Scan on faqs (cost=43.00 rows=1000 width=4)
EXPLAIN
faqtat=> explain select distinct on id faqs.id from faqs, faq_keywords;
NOTICE: QUERY PLAN:
Unique (cost=43.00 rows=1000 width=4)
-> Sort (cost=43.00 rows=1000 width=4)
-> Seq Scan on faqs (cost=43.00 rows=1000 width=4)
EXPLAIN
faqtat=>
No file was uploaded with this report
