Ken Tanzer wrote: > On Mon, Aug 6, 2018 at 4:11 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Ken Tanzer <ken.tanzer@xxxxxxxxx> writes: > > > Hi. I was recently troubleshooting a function, and realized it had > > > incorrectly been declared as Immutable, when it should have been declared > > > Stable. When I changed it to Stable, the query I was running ran > > > dramatically faster. Digging into this a little more, this is what I found: > > spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_imm(client_id) FROM tbl_residence_own; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------- > Seq Scan on public.tbl_residence_own (cost=0.00..1990.02 rows=6977 width=8) (actual time=3.771..22665.604 rows=6983 loops=1) > Output: client_id, si_imm(client_id) > Buffers: shared hit=199814 > Planning time: 0.156 ms > Execution time: 22677.333 ms > (5 rows) > > spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_stable(client_id) FROM tbl_residence_own; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------ > Seq Scan on public.tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) (actual time=3.100..1302.888 rows=6983 loops=1) > Output: client_id, staff_inspector_stable(client_id, target_date()) > Buffers: shared hit=60174 > Planning time: 0.354 ms > Execution time: 1315.746 ms > (5 rows) That seems to say that the _stable function is running much faster. Buth functions don't get inlined. I'd dig into the functions and find out how long the queries in them take. auto_explain is a good helper for that. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
