X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DAD4FD1B9A9 for ; Thu, 26 Feb 2004 13:25:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 16795-08 for ; Thu, 26 Feb 2004 09:25:27 -0400 (AST) Received: from zenon.apartia.com (zenon.apartia.fr [82.66.93.83]) by svr1.postgresql.org (Postfix) with ESMTP id 00018D1B901 for ; Thu, 26 Feb 2004 09:25:23 -0400 (AST) Received: from argos (argos.apartia.fr [10.0.2.105]) by zenon.apartia.com (Postfix) with ESMTP id 55C55133934 for ; Thu, 26 Feb 2004 14:25:26 +0100 (CET) Received: by argos (Postfix, from userid 1018) id 280E3115F7; Thu, 26 Feb 2004 14:30:38 +0100 (CET) Date: Thu, 26 Feb 2004 14:30:38 +0100 From: David Pradier To: pgsql-performance@postgresql.org Subject: A cache for the results of queries ? Message-ID: <20040226133038.GA1481@apartia.fr> Mail-Followup-To: pgsql-performance@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/297 X-Sequence-Number: 5821 Hi everybody, i'd like to know if it exists a system of cache for the results of queries. What i'd like to do : select whatever_things from (selection_in_cache) where special_conditions; The interesting thing would be to have a precalculated selection_in_cache, especially when selection_in_cache is a very long list of joins... For example, a real case: SELECT p.id_prospect, p.id_personne1, INITCAP(p1.nom) AS nom, INITCAP(p1.prenom) AS prenom, a1.no_tel, a1.no_portable, p.dernier_contact, cn.id_contact, cn.id_vendeur, cn.id_operation, CASE WHEN p.dernier_contact IS NOT NULL THEN cn.date_contact::ABSTIME::INT4 ELSE p.cree_le::ABSTIME::INT4 END AS date_contact, cn.id_moyen_de_contact, cn.id_type_evenement, cn.nouveau_rdv::ABSTIME::INT4 AS nouveau_rdv, cn.date_echeance::ABSTIME::INT4 AS date_echeance, cn.date_reponse::ABSTIME::INT4 AS date_reponse, (CASE WHEN lower(cn.type_reponse) = '.qs( 'abandon' ).' AND cn.id_vendeur = '.qs( $login ).' THEN '.qs( 'O').' ELSE p.abandon END) AS abandon FROM prospect p JOIN personne p1 ON (p.id_personne1 = p1.id_personne) JOIN adresse a1 ON (a1.id_adresse = p1.id_adresse_principale) LEFT JOIN contact cn ON (p.dernier_contact = cn.id_contact) '.( $type_orig ? 'LEFT JOIN orig_pub op ON ( p.id_orig_pub = op.id_orig_pub )' : '' ).' WHERE ( '.( $abandon ? '' : '( (cn.type_reponse IS NULL OR lower(cn.type_reponse) != ' .qs( 'abandon' ) .' OR cn.id_vendeur != ' .qs( $login ) .') AND (p.abandon != ' .qs( 'O' ) .' OR p.abandon IS NULL)) AND ' ).' TRUE '.$condition.') ORDER BY '.$primary_sort.', '.secondary_sort.' LIMIT 30 OFFSET '.$offset*$page_length There is some perl inside to generate the query ; for non-perl-people, '.' is used for concatenation and '( a ? b : c)' means 'if a then b else c'. $condition is a precalculated set of conditions. Here i have a very heavy query with 4 very heavy JOIN. That's why i'd like to have a precalculated query. A view wouldn't help, because it would calculate the whole query each time. Any idea ? Thanks in advance for any input. -- dpradier@apartia.fr 01.46.47.21.33 fax: 01.45.20.17.98