The following bug has been logged online:
Bug reference: 2515
Logged by: Daniel Naschenweng
Email address: daniel@totall.com.br
PostgreSQL version: 8.0.7
Operating system: Red Hat Linux 3.2.3-47.3
Description: Full Scan with constant column
Details:
--- BEGIN CREATE CONTEXT ---
drop table tab1 cascade;
drop table tab2 cascade;
CREATE TABLE TAB1 (
TAB1_ID SERIAL CONSTRAINT PK_TAB1_ID PRIMARY KEY,
VALOR INTEGER
);
CREATE TABLE TAB2 (
TAB2_ID SERIAL CONSTRAINT PK_TAB2_ID PRIMARY KEY,
TAB1_ID INTEGER,
CONSTRAINT FK_TAB1_TAB2 FOREIGN KEY (TAB1_ID) REFERENCES TAB1 (TAB1_ID)
);
CREATE OR REPLACE FUNCTION POPULA_TAB ()
RETURNS NAME AS '
DECLARE
I INTEGER;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO TAB1 (TAB1_ID,VALOR) VALUES (I,I);
INSERT INTO TAB2 (TAB1_ID) VALUES (I);
END LOOP;
RETURN ''OK'';
END;
' language 'plpgsql';
SELECT POPULA_TAB();
--- END CREATE CONTEXT ---
/* Select Seq Scan on tab2: */
explain
select t2.*
FROM tab1 t1 LEFT OUTER JOIN (select tab2.*
, 1 as coluna
from tab2
) t2 on t1.tab1_id=t2.tab2_id
WHERE t1.tab1_id=200;
QUERY PLAN
----------------------------------------------------------------------------
----
Nested Loop Left Join (cost=0.00..3958.01 rows=1 width=12)
Join Filter: ("outer".tab1_id = "inner".tab2_id)
-> Index Scan using pk_tab1_id on tab1 t1 (cost=0.00..6.01 rows=1
width=4)
Index Cond: (tab1_id = 200)
-> Subquery Scan t2 (cost=0.00..2640.08 rows=104954 width=12)
-> Seq Scan on tab2 (cost=0.00..1590.54 rows=104954 width=8)
(6 rows)
/* Correct plain on tab2: */
explain
select t2.*
FROM tab1 t1 LEFT OUTER JOIN (select tab2.*
--, 1 as coluna
from tab2
) t2 on t1.tab1_id=t2.tab2_id
WHERE t1.tab1_id=200;
QUERY PLAN
----------------------------------------------------------------------------
----
Nested Loop Left Join (cost=0.00..12.03 rows=1 width=8)
-> Index Scan using pk_tab1_id on tab1 t1 (cost=0.00..6.01 rows=1
width=4)
Index Cond: (tab1_id = 200)
-> Index Scan using pk_tab2_id on tab2 (cost=0.00..6.01 rows=1
width=8)
Index Cond: ("outer".tab1_id = tab2.tab2_id)
(5 rows)
