pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Pradier <dpradier@apartia.fr>
To: pgsql-performance@postgresql.org
Subject: A cache for the results of queries ?
Date: Thu, 26 Feb 2004 14:30:38 +0100
Message-ID: <20040226133038.GA1481@apartia.fr> (raw)

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



view thread (4+ messages)  latest in thread

Message-ID: <20040226133038.GA1481@apartia.fr>
Permalink:  ../20040226133038.GA1481@apartia.fr/
Also on:    postgresql.org/message-id/20040226133038.GA1481@apartia.fr

 · 

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-performance@postgresql.org
  Cc: dpradier@apartia.fr
  Subject: Re: A cache for the results of queries ?
  In-Reply-To: <20040226133038.GA1481@apartia.fr>

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

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