-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello.
Thanks to the indications given by Tom Lane, I was able to make use of
parameterized path in our multicorn extension.
For example, given a (local) table "real_table" and another (foreign)
table "foreign_table", having the same set of columns, if the foreign
table declares that a filter on col1 and col2 will, on average, return
100 rows (vs 1million for an unparameterized path), postgresql will
produce the following plan:
explain select real_table.*, foreign_table.*from real_table inner join foreign_table on real_table.col1 =
foreign_table.col1and real_table.col2 = foreign_table.col2;
QUERY PLAN
-
-----------------------------------------------------------------------------------------Nested Loop
(cost=10.00..19145165.27rows=50558 width=64) -> Seq Scan on real_table (cost=0.00..38.27 rows=2127 width=32) ->
ForeignScan on foreign_table (cost=10.00..9000.00 rows=100
width=90) Filter: ((real_table.col1 = (col1)::text) AND
(real_table.col2 = (col2)::text))
This is exactly what I wanted to achieve.
But there is still room for improvement here, because the inner
foreign scan will be executed for each row, and not for each distinct
couple of col1, col2.
Does this happen because:- there is no other way to proceed.- the planner thinks it is less costly to execute the inner
scan
multiple times than to do whatever it takes to execute it only one
time for each distinct couple ?
Best regards,
- --
Ronan Dunklau
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.19 (GNU/Linux)
iQEcBAEBAgAGBQJQAEbxAAoJECTYLCgFy323FTUH/j81AAT1ODBdizIdTV+yI7nX
KjCg+hBwTlKMs8l8KUuslEo0wp3Wc8Yem0PFCvO3+0IYZ26iGsi5jIoqflaZ86gZ
MAjRoUyXfn3Maz/vU3TIYYwYnWhMp1i4GwFf6bqXaVlCVYAaARetksxc5o52lZZT
cgN/D1wek6FQkKSSN916siuIwlkEIHiMkB3VF2up1veRtzPbOvvosmdAKyYaMAcH
auqOBu/PVMUkR/5g/HbqkK+DoN3PYXpUw7LWPfoAQHEYijCPdR9De9BnJGW4RZL2
j2xkixJSR0h8KYkgH6WIAXTfbz1/l9GFXe0mJFskfU42mGmpLO41YeqhbSHaNtw=
=SiMC
-----END PGP SIGNATURE-----
