From: | Gaetano Mendola <mendola(at)bigfoot(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Subject: | Outer where pushed down |
Date: | 2005-10-06 11:13:57 |
Message-ID: | 434506F5.10608@bigfoot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi all,
consider this view:
CREATE OR REPLACE VIEW v_current_connection AS
SELECT ul.id_user
FROM user_login ul,
current_connection cc
WHERE ul.id_user = cc.id_user;
And this is the explain on a usage of that view:
# explain select * from v_current_connection_test where sp_connected_test(id_user) = FALSE;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Hash Join (cost=42.79..1325.14 rows=451 width=5)
Hash Cond: ("outer".id_user = "inner".id_user)
-> Seq Scan on user_login ul (cost=0.00..1142.72 rows=27024 width=4)
Filter: (sp_connected_test(id_user) = false)
-> Hash (cost=40.49..40.49 rows=919 width=5)
-> Index Scan using idx_connected on current_connection cc (cost=0.00..40.49 rows=919 width=5)
Index Cond: (connected = true)
(7 rows)
apart my initial surprise to see that function applied at rows not returned by the view
( Tom Lane explained me that the planner is able to push down the outer condition )
why postgres doesn't apply that function at table current_connection given the fact are extimated
only 919 vs 27024 rows?
redefining the view:
CREATE OR REPLACE VIEW v_current_connection AS
SELECT cc.id_user
FROM user_login ul,
current_connection cc
WHERE ul.id_user = cc.id_user;
then I obtain the "desidered" plan.
# explain select * from v_current_connection_test where sp_connected_test(id_user ) = FALSE;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Hash Join (cost=46.23..1193.47 rows=452 width=5)
Hash Cond: ("outer".id_user = "inner".id_user)
-> Seq Scan on user_login ul (cost=0.00..872.48 rows=54048 width=4)
-> Hash (cost=45.08..45.08 rows=460 width=5)
-> Index Scan using idx_connected on current_connection cc (cost=0.00..45.08 rows=460 width=5)
Index Cond: (connected = true)
Filter: (sp_connected_test(id_user) = false)
(7 rows)
Is not possible in any way push postgres to apply that function to the right table ?
Shall I rewrite the views figuring out wich column is better to expose ?
Regards
Gaetano Mendola
From | Date | Subject | |
---|---|---|---|
Next Message | Esha Palta | 2005-10-06 12:26:40 | case insensitive joining in case of nested loop joins |
Previous Message | Zeugswetter Andreas DAZ SD | 2005-10-06 10:56:55 | Re: [PERFORM] A Better External Sort? |