Skip site navigation (1) Skip section navigation (2)

Polyplanner (was Re: Avoiding bad prepared-statement plans.)

From: Yeb Havinga <yebhavinga(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, Mark Mielke <mark(at)mark(dot)mielke(dot)cc>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alex Hunsaker <badalex(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Bart Samwel <bart(at)samwel(dot)tk>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Subject: Polyplanner (was Re: Avoiding bad prepared-statement plans.)
Date: 2010-03-01 11:18:01
Message-ID: 4B8BA269.1060100@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
How about a totally different approach?

What if all queries and plans of all queries, simple and prepared, were 
pre-planned and cached always, persistent?
For prepared statements with >= 1 parameters, histogram and mcv 
information could be used to search the plan space for interesting 
plans. Maybe with some heuristics to cut down on search space (i.e. when 
operator is '=' and there is a unique index, skip that clause / 
parameter from the search space).
Since processors keep getting more and more cores, and most database 
activity is IO bound, why not keep one core busy with query analysis?

good:
- with the several hooks available it could be implemented as optional 
contrib
- if offers plan stability
- nice info for management user interface
- might be a solution for prepared queries
- for queries with large joins, plans might be considered with 
exhaustive search, so also here there could be an improvement.
- it might even be possible to 'test' plans during low-usage hours

bad:
- unknown how big space for cached plans should be
- if big cached plan space doesn't fit in memory, actual planning 
probably better than fetching from disk, ~= 5 to 10ms.

regards,
Yeb Havinga

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2010-03-01 13:13:14
Subject: Re: Linux start script updates
Previous:From: Hiroshi InoueDate: 2010-03-01 11:00:15
Subject: Re: [GENERAL] trouble with to_char('L')

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group