I loaded a copy of a production database into PG 8.4b1 and immediately
saw that all of our queries were significantly slower compared to v8.1.
Some investigation showed that the use of non-IMMUTABLE PL/PGSQL
functions as view columns, when these views are joined with other
views, cause the query to be planned poorly.
Attached are the two different plans. Literally, the only difference
is changing the definition of the custom PL/PGSQL to be IMMUTABLE.
I spent some time coming up with a reproduce-able schema, but it's
almost 500k gzipped. Is that too big to attach to -hackers? The
function in the test schema is simply:
CREATE FUNCTION make_it_slow(id bigint) RETURNS text
LANGUAGE plpgsql AS $$begin return 'non-immutable functions make
it slow'; end;$$;
In our case, the suspect functions *can* be declared IMMUTABLE, and we
should have done that in the first place, but I thought it was worth
mentioning that v8.1 did a much better job planning in this particular
case.
If my test schema will be beneficial, please let me know.
Thanks!
eric
foo=# explain analyze select count(*) from c where tab_id = 2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2014181.55..2014181.56 rows=1 width=0) (actual
time=524.034..524.035 rows=1 loops=1)
-> Hash Join (cost=2316.92..2014180.53 rows=407 width=0) (actual
time=436.223..524.026 rows=1 loops=1)
Hash Cond: (item.id = folder.id)
-> Hash Join (cost=2311.61..2013055.97 rows=81398 width=1313)
(actual time=51.783..508.441 rows=81398 loops=1)
Hash Cond: (perms.owner_id = "user".id)
-> Hash Join (cost=2310.45..5678.39 rows=81398 width=1281)
(actual time=50.485..204.430 rows=81398 loops=1)
Hash Cond: (perms.item_id = item.id)
-> Seq Scan on perms (cost=0.00..1332.98 rows=81398
width=17) (actual time=0.041..37.544 rows=81398 loops=1)
Filter: (NOT deleted)
-> Hash (cost=1292.98..1292.98 rows=81398 width=1272)
(actual time=50.389..50.389 rows=81398 loops=1)
-> Seq Scan on item (cost=0.00..1292.98 rows=81398
width=1272) (actual time=0.038..22.298 rows=81398 loops=1)
-> Hash (cost=1.07..1.07 rows=7 width=40) (actual
time=0.017..0.017 rows=7 loops=1)
-> Seq Scan on "user" (cost=0.00..1.07 rows=7 width=40)
(actual time=0.013..0.014 rows=7 loops=1)
SubPlan 1
-> Aggregate (cost=24.39..24.40 rows=1 width=0) (never
executed)
-> Seq Scan on comments (cost=0.00..24.38 rows=3
width=0) (never executed)
Filter: (read AND (item_id = $0))
-> Hash (cost=5.29..5.29 rows=1 width=16) (actual time=0.157..0.157
rows=1 loops=1)
-> Nested Loop (cost=0.00..5.29 rows=1 width=16) (actual
time=0.152..0.154 rows=1 loops=1)
-> Seq Scan on collection (cost=0.00..1.01 rows=1
width=8) (actual time=0.023..0.024 rows=1 loops=1)
Filter: (tab_id = 2)
-> Index Scan using folder_pkey on folder
(cost=0.00..4.27 rows=1 width=8) (actual time=0.125..0.126 rows=1 loops=1)
Index Cond: (folder.id = collection.id)
Total runtime: 525.447 ms
(24 rows)
foo=# explain analyze select count(*) from c where tab_id = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=14.15..14.16 rows=1 width=0) (actual time=0.070..0.071 rows=1
loops=1)
-> Nested Loop (cost=0.00..14.14 rows=1 width=0) (actual time=0.048..0.053
rows=1 loops=1)
-> Nested Loop (cost=0.00..9.86 rows=1 width=24) (actual
time=0.042..0.045 rows=1 loops=1)
-> Nested Loop (cost=0.00..9.58 rows=1 width=32) (actual
time=0.038..0.040 rows=1 loops=1)
-> Nested Loop (cost=0.00..5.30 rows=1 width=24) (actual
time=0.030..0.032 rows=1 loops=1)
-> Seq Scan on collection (cost=0.00..1.01 rows=1
width=8) (actual time=0.011..0.012 rows=1 loops=1)
Filter: (tab_id = 2)
-> Index Scan using perms_pkey on perms
(cost=0.00..4.27 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (perms.item_id = collection.id)
Filter: (NOT perms.deleted)
-> Index Scan using item_pkey on item (cost=0.00..4.27
rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: (item.id = perms.item_id)
-> Index Scan using user_pkey on "user" (cost=0.00..0.27
rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
Index Cond: ("user".id = perms.owner_id)
-> Index Scan using folder_pkey on folder (cost=0.00..4.27 rows=1
width=8) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (folder.id = perms.item_id)
Total runtime: 0.296 ms
(17 rows)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
