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.
--
[EMAIL PROTECTED] 01.46.47.21.33 fax: 01.45.20.17.98
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
