From: | "Pedro J(dot) Romero" <multiacademia(at)eresmas(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #2487: Immutable functions results |
Date: | 2006-06-20 14:21:37 |
Message-ID: | 200606201421.k5KELbIe057636@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 2487
Logged by: Pedro J. Romero
Email address: multiacademia(at)eresmas(dot)com
PostgreSQL version: 8.0.1
Operating system: Linux
Description: Immutable functions results
Details:
Sorry if this not a bug, but I think so:
Look at this, I think this must be self explanatory, after two or three
readings:
select * from tablea;
fielda
------
16
(1 row)
select * from childtablea;
fielda|data_a
------|----------
16| 101
16| 127
16| 4315
(3 rows)
select fielda, otherfunction('childtablea', 'fielda', fielda) from tablea;
--otherfunction is not marked as immutable, it cannot
--So I cannot use it for index
fielda|otherfunction
------|-----------------
16|101-127-4315
(1 row)
select fielda, immutablefunction_a(fielda) from tablea;
fielda|immutablefunction_a
------|-------------------
16|101-127-4315
(1 row)
select otherfunction('childtablea', 'fielda', fielda),
immutablefunction_a(fielda), otherfunction('childtablea', 'fielda',
fielda)=immutablefunction_a(fielda) as are_the_same from tablea;
otherfunction|immutablefunction_a|are_the_same
-------------|-------------------|------------
101-127-4315 |101-127-4315 |t
--===========================================
--===========================================
--Same schema, same data, same database, other tables
select * from tableb;
fieldb
------
16
(1 row)
select * from childtableb;
fieldb|data_b
------|----------
16| 101
16| 127
16| 4315
(3 rows)
select fieldb,otherfunction('childtableb', 'fieldb', fieldb) from tableb;
fieldb|otherfunction
------|-----------------
16|101-127-4315
(1 row)
select fieldb, immutablefunction_b(fieldb) from tableb;
fieldb|immutablefunction
------|-----------------
16|101-127-4315
(1 row)
select otherfunction('childtableb', 'fieldb', fieldb),
immutablefunction_b(fieldb), otherfunction('childtableb', 'detailb',
fieldb)=immutablefunction_b(fieldb) as are_the_same from tableb;
otherfunction|immutablefunction_b|are_the_same
-------------|-------------------|------------
101-127-4315 |101-127-4315 |t
--===========================================
--===========================================
Ok, until this....
But....
vacuum full tablea;
vacuum full tableb;
select fielda, fieldb, otherfunction('childtablea', 'fielda', fielda) from
tablea left join tableb on otherfunction('childtablea', 'fielda',
fielda)=otherfunction('childtableb', 'fieldb', fieldb);
fielda|fieldb|otherfunction
------|------|-------------
16| 16|101-127-4315
(0 rows)
select fielda, fieldb from tablea left join tableb on
immutablefunction_a(fielda)=immutablefunction_b(fieldb);
fielda|fieldb
------|------
(0 rows)
vacuum full tablea;
vacuum full tableb;
select fielda, fieldb from tablea left join tableb on
immutablefunction_a(fielda)=immutablefunction_b(fieldb);
fielda|fieldb
------|------
(0 rows)
Why? If I cannot use the immutable function, performance is really bad.
Using an index, speed is several times greater.
Is this a bug? Or I'm missing something about the immutable functions
characteristics?
Thank you very much.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2006-06-21 02:25:00 | Re: clarification.. |
Previous Message | Dhanaraj M | 2006-06-20 11:28:23 | clarification.. |