On Mon, Aug 6, 2018 at 4:11 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Ken Tanzer <ken.tan...@gmail.com> 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:
>
> EXPLAIN VERBOSE might shed a bit more light. I suspect you're falling
> foul of the rather arcane rules about whether a SQL function can be
> inlined. (Although it seems like only the wrapper function would be
> inline-able anyway, so I'm not sure why the penalty is that high.)
>
> regards, tom lane
>
Thanks Tom. This was as talkative as I could get it. I do see the
difference on the Output line, but I don't know how to interperet it.
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)
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-requ...@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
