Message-ID: <4423EACD.80706@krap.dk>
Date: Fri, 24 Mar 2006 13:49:17 +0100
From: Svenne Krap <svenne@krap.dk>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Performance problems with multiple layers of functions
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature";
	micalg=sha1; boundary="------------ms010808000203030803030100"

This is a cryptographically signed message in MIME format.

--------------ms010808000203030803030100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi there.

I have hit a edge in the planning and I hope you can help.

The system uses a lot of stored procedures to move as much of the 
intelligence into the database layer as possible.

My (development) query looks like and runs reasonably fast:

explain analyze select dataset_id, entity, sum(amount) from 
entrydata_current where  flow_direction in (select * from 
outflow_direction(dataset_id)) and dataset_id in (122,112,125,89,111) 
group by dataset_id, entity;
                                                                           
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=918171.00..918171.30 rows=24 width=19) (actual 
time=11533.297..11533.340 rows=50 loops=1)
   ->  Bitmap Heap Scan on entrydata_current  (cost=676.72..917736.04 
rows=57994 width=19) (actual time=23.921..11425.373 rows=37870 loops=1)
         Recheck Cond: ((dataset_id = 122) OR (dataset_id = 112) OR 
(dataset_id = 125) OR (dataset_id = 89) OR (dataset_id = 111))
         Filter: (subplan)
         ->  BitmapOr  (cost=676.72..676.72 rows=117633 width=0) (actual 
time=15.765..15.765 rows=0 loops=1)
               ->  Bitmap Index Scan on entrydata_current_dataset_idx  
(cost=0.00..83.97 rows=14563 width=0) (actual time=1.881..1.881 
rows=13728 loops=1)
                     Index Cond: (dataset_id = 122)
               ->  Bitmap Index Scan on entrydata_current_dataset_idx  
(cost=0.00..156.12 rows=27176 width=0) (actual time=3.508..3.508 
rows=25748 loops=1)
                     Index Cond: (dataset_id = 112)
               ->  Bitmap Index Scan on entrydata_current_dataset_idx  
(cost=0.00..124.24 rows=21498 width=0) (actual time=2.729..2.729 
rows=20114 loops=1)
                     Index Cond: (dataset_id = 125)
               ->  Bitmap Index Scan on entrydata_current_dataset_idx  
(cost=0.00..102.20 rows=17771 width=0) (actual time=2.351..2.351 
rows=17344 loops=1)
                     Index Cond: (dataset_id = 89)
               ->  Bitmap Index Scan on entrydata_current_dataset_idx  
(cost=0.00..210.19 rows=36625 width=0) (actual time=5.292..5.292 
rows=37118 loops=1)
                     Index Cond: (dataset_id = 111)
         SubPlan
           ->  Function Scan on outflow_direction  (cost=0.00..12.50 
rows=1000 width=4) (actual time=0.093..0.095 rows=4 loops=114052)
 Total runtime: 11540.506 ms
(18 rows)

The problem is, that the application should not need to know the five 
dataset_ids (it will always know one - its own). So I make a function to 
return the five ids and then the query looks like:

explain select dataset_id, entity, sum(amount) from entrydata_current 
where  flow_direction in (select * from outflow_direction(dataset_id)) 
and dataset_id in (select * from get_dataset_ids(122)) group by 
dataset_id, entity;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=24672195.68..24672203.88 rows=656 width=19)
   ->  Hash IN Join  (cost=15.00..24660005.45 rows=1625364 width=19)
         Hash Cond: ("outer".dataset_id = "inner".get_dataset_ids)
         ->  Index Scan using entrydata_current_dataset_idx on 
entrydata_current  (cost=0.00..24558405.20 rows=1625364 width=19)
               Filter: (subplan)
               SubPlan
                 ->  Function Scan on outflow_direction  
(cost=0.00..12.50 rows=1000 width=4)
         ->  Hash  (cost=12.50..12.50 rows=1000 width=4)
               ->  Function Scan on get_dataset_ids  (cost=0.00..12.50 
rows=1000 width=4)
(9 rows)

which does not return within 10 minutes - which is unacceptable.

Is there any way to get a better plan for the second ? The planner 
should really see the two queries as equal as there is no dependencies 
between the outer query and get_dataset_ids (isn't it called constant 
folding?).

Thanks in advance

Svenne

--------------ms010808000203030803030100
Content-Type: application/x-pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIKTjCC
BSMwggQLoAMCAQICBD/KpecwDQYJKoZIhvcNAQEFBQAwMTELMAkGA1UEBhMCREsxDDAKBgNV
BAoTA1REQzEUMBIGA1UEAxMLVERDIE9DRVMgQ0EwHhcNMDUwMjIzMTMxMTMwWhcNMDcwMjIz
MTM0MTMwWjBzMQswCQYDVQQGEwJESzEpMCcGA1UEChMgSW5nZW4gb3JnYW5pc2F0b3Jpc2sg
dGlsa255dG5pbmcxOTASBgNVBAMTC1N2ZW5uZSBLcmFwMCMGA1UEBRMcUElEOjk4MDItMjAw
Mi0yLTk3OTMxMTk0MjAyNzCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEApF423aqlHV/8
RiX5vX5QezMEXKixU6KUSUQOfERsFgahaUn5LobNfxB31+5nKVH3EJfK/UpcE4Alfsa87+hX
MBRzaAv5jbwh57NFSNcmFD/wgcIYupvyWhASacjcBnBfiqNuDBVIT7wL42yijfM3LSVjJOPw
/c5R5wwFfKqjKzMCAwEAAaOCAoMwggJ/MA4GA1UdDwEB/wQEAwID+DArBgNVHRAEJDAigA8y
MDA1MDIyMzEzMTEzMFqBDzIwMDcwMjIzMTM0MTMwWjCCATcGA1UdIASCAS4wggEqMIIBJgYK
KoFQgSkBAQEBAjCCARYwLwYIKwYBBQUHAgEWI2h0dHA6Ly93d3cuY2VydGlmaWthdC5kay9y
ZXBvc2l0b3J5MIHiBggrBgEFBQcCAjCB1TAKFgNUREMwAwIBARqBxkZvciBhbnZlbmRlbHNl
IGFmIGNlcnRpZmlrYXRldCBn5mxkZXIgT0NFUyB2aWxr5XIsIENQUyBvZyBPQ0VTIENQLCBk
ZXIga2FuIGhlbnRlcyBmcmEgd3d3LmNlcnRpZmlrYXQuZGsvcmVwb3NpdG9yeS4gQmVt5nJr
LCBhdCBUREMgZWZ0ZXIgdmlsa+VyZW5lIGhhciBldCBiZWdy5m5zZXQgYW5zdmFyIGlmdC4g
cHJvZmVzc2lvbmVsbGUgcGFydGVyLjAZBgNVHREEEjAQgQ5zdmVubmVAa3JhcC5kazCBgwYD
VR0fBHwwejBKoEigRqREMEIxCzAJBgNVBAYTAkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMT
C1REQyBPQ0VTIENBMQ8wDQYDVQQDEwZDUkw1NjgwLKAqoCiGJmh0dHA6Ly9jcmwub2Nlcy5j
ZXJ0aWZpa2F0LmRrL29jZXMuY3JsMB8GA1UdIwQYMBaAFGC1hexWZH4SGSdnHVAVS3OuO/kS
MB0GA1UdDgQWBBTmRrmEbRpZf+Pt0S/qphnyMgKoRzAJBgNVHRMEAjAAMBkGCSqGSIb2fQdB
AAQMMAobBFY3LjEDAgOoMA0GCSqGSIb3DQEBBQUAA4IBAQBgfOEd/VkXc6lFNItNvhgrbPc3
eNcGE7l6sbaG1gYLREn4xmmYajtP90INRG3OlNm3ZlVeYxaMbzQKVlcrKMEwA7cMZIMcT97t
KjXabwqCc/0jNCPceO/ZPoUc7T3fuF7B1QjJelzXsoK4av2lDDgaAJL8WiExsGYmJ0BwfNLZ
YjLRwJ+Db4gsmvjTFazbkRtzqqqAzjy2WC6L4WEuS6EUmDhdX/ZFdq0bT58lx++DKo+TuDu3
bkIi46l6yV5lI4CgBRTmahRyyCuUMjDUiLvgX1m3Ys/U5c88MDgwrKtLcXy7TyCEazOLVC5z
vAgKvNjbdSQjkFZEDiA/oVtwGNH6MIIFIzCCBAugAwIBAgIEP8ql5zANBgkqhkiG9w0BAQUF
ADAxMQswCQYDVQQGEwJESzEMMAoGA1UEChMDVERDMRQwEgYDVQQDEwtUREMgT0NFUyBDQTAe
Fw0wNTAyMjMxMzExMzBaFw0wNzAyMjMxMzQxMzBaMHMxCzAJBgNVBAYTAkRLMSkwJwYDVQQK
EyBJbmdlbiBvcmdhbmlzYXRvcmlzayB0aWxrbnl0bmluZzE5MBIGA1UEAxMLU3Zlbm5lIEty
YXAwIwYDVQQFExxQSUQ6OTgwMi0yMDAyLTItOTc5MzExOTQyMDI3MIGfMA0GCSqGSIb3DQEB
AQUAA4GNADCBiQKBgQCkXjbdqqUdX/xGJfm9flB7MwRcqLFTopRJRA58RGwWBqFpSfkuhs1/
EHfX7mcpUfcQl8r9SlwTgCV+xrzv6FcwFHNoC/mNvCHns0VI1yYUP/CBwhi6m/JaEBJpyNwG
cF+Ko24MFUhPvAvjbKKN8zctJWMk4/D9zlHnDAV8qqMrMwIDAQABo4ICgzCCAn8wDgYDVR0P
AQH/BAQDAgP4MCsGA1UdEAQkMCKADzIwMDUwMjIzMTMxMTMwWoEPMjAwNzAyMjMxMzQxMzBa
MIIBNwYDVR0gBIIBLjCCASowggEmBgoqgVCBKQEBAQECMIIBFjAvBggrBgEFBQcCARYjaHR0
cDovL3d3dy5jZXJ0aWZpa2F0LmRrL3JlcG9zaXRvcnkwgeIGCCsGAQUFBwICMIHVMAoWA1RE
QzADAgEBGoHGRm9yIGFudmVuZGVsc2UgYWYgY2VydGlmaWthdGV0IGfmbGRlciBPQ0VTIHZp
bGvlciwgQ1BTIG9nIE9DRVMgQ1AsIGRlciBrYW4gaGVudGVzIGZyYSB3d3cuY2VydGlmaWth
dC5kay9yZXBvc2l0b3J5LiBCZW3mcmssIGF0IFREQyBlZnRlciB2aWxr5XJlbmUgaGFyIGV0
IGJlZ3LmbnNldCBhbnN2YXIgaWZ0LiBwcm9mZXNzaW9uZWxsZSBwYXJ0ZXIuMBkGA1UdEQQS
MBCBDnN2ZW5uZUBrcmFwLmRrMIGDBgNVHR8EfDB6MEqgSKBGpEQwQjELMAkGA1UEBhMCREsx
DDAKBgNVBAoTA1REQzEUMBIGA1UEAxMLVERDIE9DRVMgQ0ExDzANBgNVBAMTBkNSTDU2ODAs
oCqgKIYmaHR0cDovL2NybC5vY2VzLmNlcnRpZmlrYXQuZGsvb2Nlcy5jcmwwHwYDVR0jBBgw
FoAUYLWF7FZkfhIZJ2cdUBVLc647+RIwHQYDVR0OBBYEFOZGuYRtGll/4+3RL+qmGfIyAqhH
MAkGA1UdEwQCMAAwGQYJKoZIhvZ9B0EABAwwChsEVjcuMQMCA6gwDQYJKoZIhvcNAQEFBQAD
ggEBAGB84R39WRdzqUU0i02+GCts9zd41wYTuXqxtobWBgtESfjGaZhqO0/3Qg1Ebc6U2bdm
VV5jFoxvNApWVysowTADtwxkgxxP3u0qNdpvCoJz/SM0I9x479k+hRztPd+4XsHVCMl6XNey
grhq/aUMOBoAkvxaITGwZiYnQHB80tliMtHAn4NviCya+NMVrNuRG3OqqoDOPLZYLovhYS5L
oRSYOF1f9kV2rRtPnyXH74Mqj5O4O7duQiLjqXrJXmUjgKAFFOZqFHLIK5QyMNSIu+BfWbdi
z9TlzzwwODCsq0txfLtPIIRrM4tULnO8CAq82Nt1JCOQVkQOID+hW3AY0foxggIqMIICJgIB
ATA5MDExCzAJBgNVBAYTAkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMTC1REQyBPQ0VTIENB
AgQ/yqXnMAkGBSsOAwIaBQCgggFHMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZI
hvcNAQkFMQ8XDTA2MDMyNDEyNDkxN1owIwYJKoZIhvcNAQkEMRYEFIxksC3WBd7kLBZdEDT4
MtKbmbPqMEgGCSsGAQQBgjcQBDE7MDkwMTELMAkGA1UEBhMCREsxDDAKBgNVBAoTA1REQzEU
MBIGA1UEAxMLVERDIE9DRVMgQ0ECBD/KpecwSgYLKoZIhvcNAQkQAgsxO6A5MDExCzAJBgNV
BAYTAkRLMQwwCgYDVQQKEwNUREMxFDASBgNVBAMTC1REQyBPQ0VTIENBAgQ/yqXnMFIGCSqG
SIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFA
MAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMA0GCSqGSIb3DQEBAQUABIGAT1oCeymBX6knJsK5
FBH0FiKc8pp9sfdbtHoEaC8xp4mwbHz8RUZjMWqn99trdBjcxrrNcxE7DaHVetmj9ApP9AkS
Hmt5HMs/llKz4pajtJG+QnFnWxrJ0kt14+wvSQyMFC0GWv+JrtPAjIXXQLvrnqI/4FsRRWQY
zZZI0CypSvgAAAAAAAA=
--------------ms010808000203030803030100--