agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Query caching (with 8.3)
Date: Mon, 16 Dec 2013 12:58:56 +0200
Message-ID: <52AEDCF0.8000606@matrix.gatewaynet.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (8+ messages)  latest in thread

Message-ID: <52AEDCF0.8000606@matrix.gatewaynet.com>
Permalink:  ../52AEDCF0.8000606@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/52AEDCF0.8000606@matrix.gatewaynet.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com
  Subject: Re: Query caching (with 8.3)
  In-Reply-To: <52AEDCF0.8000606@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox