Search Postgresql Archives

Re: Immutable function WAY slower than Stable function?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Aug 6, 2018 at 11:05 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
Ken Tanzer wrote:
> On Mon, Aug 6, 2018 at 4:11 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> > Ken Tanzer <ken.tanzer@xxxxxxxxx> writes:
>
> 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.

Agreed!
 
Buth functions don't get inlined.

OK, I clearly don't understand the output lines.  What does it mean then that the stable output line shows the underlying (two-argument) function, while the immutable one shows the convenience function?
 
Output: client_id, si_imm(client_id)
Output: client_id, staff_inspector_stable(client_id, target_date())

I'd dig into the functions and find out how long the queries in
them take.  auto_explain is a good helper for that.


I'm definitely not understanding why or how auto-explain would help here.  (Also, don't overlook the fact that both si_stable and si_imm have the exact same definition (except for stable v. immutable), and are calling the same function (staff_inspector_stable)).  I can load up the auto_explain stuff if it really will help, but in the meantime what I did was pull the query out of staff_inspector_stable, and inserted into the "SELECT client_id,staff_inspector( ... ) FROM tbl_residence_own" query, replacing the "client" (parameter 1) with the client_id from the tbl_residence_own, and replacing "asof" (parameter 2) with target_date(), which seems like it should closely mimic the original query.  

I ended up with the query below, and have attached the explain output to this email.  The actual timing is:
 Planning time: 2.737 ms
 Execution time: 821.034 ms
Which is somewhat better than the function versions, though it seems unsurprisingly and not dramatically so.

EXPLAIN (VERBOSE,ANALYZE,BUFFERS) SELECT client_id,
CASE WHEN
(SELECT program_type_code FROM reg_spc WHERE target_date() BETWEEN reg_spc_date AND COALESCE(reg_spc_date_end,target_date()) AND client_id=tro.client_id LIMIT 1)
ILIKE 'SSP%' THEN
    COALESCE((SELECT staff_id FROM staff_employment_current WHERE staff_position_code='COORD_PROP' AND agency_project_code='SSP' LIMIT 1),(SELECT staff_id FROM staff_employment_current WHERE staff_position_code='MGRPROJ' AND agency_project_code='SSP' LIMIT 1))
ELSE
    (SELECT staff_inspector FROM tbl_residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) WHERE client_id=5538 AND target_date() BETWEEN residence_date AND COALESCE(residence_date_end,target_date()) AND NOT ro.is_deleted LIMIT 1)
END
FROM tbl_residence_own tro;

Let me know if that is not helpful.  Or if you need something from auto-explain, please help me with some more specifics.  Thanks!

Ken


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Attachment: explain.staff_inspector_query.sql
Description: Binary data


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux