On 01/17/2011 02:03 AM, Zotov wrote:
select c.id from OneRow c join abstract a on a.id=AsInteger(c.id) OneRow Contains only one row, abstract contains 22 953 500 rows AsInteger is simple function on Delphi it just return input value
Ok... there has to be some kind of misunderstanding, here. First of all, if you're trying to cast a value to an integer, there are so many built-in ways to do this, I can't imagine why you'd call a C function. The most common for your example would be ::INT.
Second, you need to understand how the optimizer works. It doesn't know what the function will return, so it has to apply the function to every row in your 'abstract' table. You can get around this by applying an index to your table with the result of your function, but to do that, you'll have to mark your function as STABLE or IMMUTABLE instead of VOLATILE.
Joining on the result of a function will always do this. The database can't know what your function will return. If you can avoid using a function in your join clause, do so.
-- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
