Re: Query caching (with 8.3)

On Mon, Dec 16, 2013 at 2:58 AM, Achilleas Mantzios
<achill(at)matrix(dot)gatewaynet(dot)com> wrote:
[...]

> BEGIN
> RETURN QUERY SELECT
> c.table_name::text,c.column_name::text,c.data_type::text FROM
> information_schema.columns c WHERE c.table_schema='public' AND c.table_name
> LIKE '%_tmp' AND c.data_type IN ('bytea','text') AND EXISTS (SELECT 1 FROM
> information_schema.columns c2 WHERE c2.table_schema='public' AND
> c2.table_name=c.table_name AND c2.column_name='xid');

[...]

> So the aim here is to speed up this query. I could materialize the result in
> some table, that i would refresh over night via cron,
> i was just wandering if there was some better way. I already made the
> function STABLE with no performance gain.
> I was also wondering if i could trick postgresql to think that the output is
> always the same by making it IMMUTABLE,
> but this also gave no performance gain.
>
> So, is there anything i could do, besides overnight materialization?

You can try to increase work_mem first, because if the returning data
set is big enough it might start working with your disk drive, that
might cause to significant slowdowns. Another thing is that, IIRC,
there were no plan caching for RETURN QUERY in PL/PgSQL, so try to
rewrite it like FOR ... LOOP RETURN NEXT ... END LOOP. IMHO, these are
the only non-quirky ways to improve things.

ps.

> Lazy replication solution.
> Since you mention it, this is installed on about 90 vessels at sea, and if
> we assume 3000 EUR (tickets only) for a
> trained person to get on board and perform the upgrade, this amounts to
> 270,000 EUR.

Wow, I just wonder how do you guys manage to support/maintain these DB
servers then?

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com