From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Query caching (with 8.3) |
Date: | 2013-12-16 10:58:56 |
Message-ID: | 52AEDCF0.8000606@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello list,
i was wondering is there is some way of speeding up results of a query in postgresql 8.3 (upgrading is not an option for the moment).
Basically this is a small function querying information_schema for tables, columns satisfying specific criteria :
CREATE OR REPLACE FUNCTION xid_tables_cols(OUT table_name TEXT, OUT column_name TEXT, OUT data_type TEXT) RETURNS SETOF record
AS $$
DECLARE
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');
RETURN;
END;
$$
LANGUAGE plpgsql STABLE;
The whole point is to be able to calculate row/columns sizes based on data type, by automatically finding all those
tables that apply to our specific technique/architecture (all tables whose name end in _tmp, and in addition who have at least one column named "xid"). This query is slow in 8.3. In 9.2 this is a
non-issue.
The above structure rarely changes, it changes only when we add new tables, ending in _tmp, and also having a column "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?
Thanx.
--
Achilleas Mantzios
From | Date | Subject | |
---|---|---|---|
Next Message | Vik Fearing | 2013-12-16 23:21:07 | Re: Query caching (with 8.3) |
Previous Message | Dean Gibson (DB Administrator) | 2013-12-13 04:31:42 | Re: NULLs and composite types |