Re: eWeek Poll: Which database is most critical to your - Mailing list pgsql-hackers

> -----Original Message-----
> From: Neil Conway [mailto:nconway@klamath.dyndns.org]
> Sent: 27 February 2002 00:24
> 
> On Tue, 2002-02-26 at 19:08, Dann Corbit wrote:
> > Statistical tools are a good idea, because they can tell us where
> > indexes should be added.  However, you cannot simply return 
> the result
> > of the previous query, because the contents may have 
> changed since the
> > last time it was executed.  It is simply invalid to do 
> that.  If some
> > other system is doing that, then it isn't a relational database.
> 
> No -- as I said, any inserts, updates or deletes that affect the table
> in question will cause a full cache flush.
> 
> > How do you know whether or not someone has affected the row that you
> > are reading?  If you do not know, then every single update, 
> insert or
> > delete will mean that you have to refresh.
> 
> Yes, that is true.
> 
> >  And not only that, you will
> > also have to track it.  For sure, it will make the whole system run 
> > more slowly rather than faster.
> 
> I don't think tracking changes imposes a lot of overhead -- it is
> relatively simple to determine if a query affects a given table.
> 
> > Very likely, it is only my limited understanding not really 
> grasping 
> > what it is that you are trying to do.  Even so,  I don't 
> think it really
> > helps even for read only queries, unless it is exactly the 
> same query
> > with the same parameter markers and everything that was 
> issued before.
> > That is very unusual.  Normally, you won't have the entire 
> query hard-
> > wired, but with allow the customer to do some sort of 
> filtering of the
> > data.
> 
> Hmmm... the more I think about it, the more unusual it would be for
> _exactly_ the same query to be repeated a lot. However, the article
> reported a significant performance gain when this feature was enabled.
> That could mean that:
> 
>     (a) the performance measurements/benchmarks used by the 
> article were
> synthetic and don't reflect real database applications
> 
>     (b) the feature MySQL implements is different than the one I am
> describing
> 
> When I get a chance I'll investigate further the technique 
> used by MySQL
> to see if (b) is the case. However, it is beginning to look like this
> isn't a good idea, overall.
> 
> Cheers,
> 
> Neil
> 
> -- 
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC
> 
>
If I understand correctly you'll be taking matching the on the original 
query string, then pulling out the previous plan, rather than doing all 
the planning again? Or where you thinking of storing the resultant tuples 
(seems far more diffcult to do effciently)?
Either way would be handy for me though as I have a number of clients who 
all basically ask the same query and then ask it again every few minutes 
to update themselves. Therefore this sounds like something that would 
improve performance for me.
Hope I've understood correctly,
- Stuart