pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
A cache for the results of queries ?
4+ messages / 4 participants
[nested] [flat]

* A cache for the results of queries ?
@ 2004-02-26 13:30 David Pradier <dpradier@apartia.fr>
  2004-02-26 14:26 ` Re: A cache for the results of queries ? Richard Huxton <dev@archonet.com>
  2004-02-26 17:15 ` Re: A cache for the results of queries ? scott.marlowe <scott.marlowe@ihs.com>
  2004-02-27 00:40 ` Re: A cache for the results of queries ? William Yu <wyu@talisys.com>
  0 siblings, 3 replies; 4+ messages in thread

From: David Pradier @ 2004-02-26 13:30 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: A cache for the results of queries ?
  2004-02-26 13:30 A cache for the results of queries ? David Pradier <dpradier@apartia.fr>
@ 2004-02-26 14:26 ` Richard Huxton <dev@archonet.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Richard Huxton @ 2004-02-26 14:26 UTC (permalink / raw)
  To: David Pradier <dpradier@apartia.fr>; pgsql-performance

On Thursday 26 February 2004 13:30, David Pradier wrote:
> 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...

You might want to search the archives for the -sql list for a message 
"Materialized View Summary" - some time this week. That's almost exactly what 
you want.

-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: A cache for the results of queries ?
  2004-02-26 13:30 A cache for the results of queries ? David Pradier <dpradier@apartia.fr>
@ 2004-02-26 17:15 ` scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 0 replies; 4+ messages in thread

From: scott.marlowe @ 2004-02-26 17:15 UTC (permalink / raw)
  To: David Pradier <dpradier@apartia.fr>; +Cc: pgsql-performance

On Thu, 26 Feb 2004, David Pradier wrote:

> Hi everybody,
> 
> i'd like to know if it exists a system of cache for the results of
> queries.

I believe there are some external libs that provide this at the 
application level.  PHP's adodb is purported to do so.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: A cache for the results of queries ?
  2004-02-26 13:30 A cache for the results of queries ? David Pradier <dpradier@apartia.fr>
@ 2004-02-27 00:40 ` William Yu <wyu@talisys.com>
  2 siblings, 0 replies; 4+ messages in thread

From: William Yu @ 2004-02-27 00:40 UTC (permalink / raw)
  To: pgsql-performance

David Pradier wrote:
> i'd like to know if it exists a system of cache for the results of
> queries.

If you are willing to do this at an application level, you could 
calculate a MD5 for every query you plan to run and then SELECT INTO a 
temporary table that's based on the MD5 sum (e.g. TMP_CACHE_45123). Next 
time somebody runs a query, check to see if that table exists already. 
Then you just have to figure out some way to know when results should be 
expired.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-02-27 00:40 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-26 13:30 A cache for the results of queries ? David Pradier <dpradier@apartia.fr>
2004-02-26 14:26 ` Richard Huxton <dev@archonet.com>
2004-02-26 17:15 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-27 00:40 ` William Yu <wyu@talisys.com>

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