Greg Stark <[EMAIL PROTECTED]> writes:
> "scott.marlowe" <[EMAIL PROTECTED]> writes:
>> Would it be possible to catch an unconstrained max(id)/min(id) and rewrite
>> it as "select id from table order by id [desc] limit1" on the fly in the
>> parser somewhere?
>> That would require fairly little c
"scott.marlowe" <[EMAIL PROTECTED]> writes:
> Would it be possible to catch an unconstrained max(id)/min(id) and rewrite
> it as "select id from table order by id [desc] limit1" on the fly in the
> parser somewhere?
>
> That would require fairly little code, and be transparent to the user.
>
Would it be possible to catch an unconstrained max(id)/min(id) and rewrite
it as "select id from table order by id [desc] limit1" on the fly in the
parser somewhere?
That would require fairly little code, and be transparent to the user.
I.e. low hanging fruit.
On 5 Sep 2003, Greg Stark wrote:
Bruce Momjian <[EMAIL PROTECTED]> writes:
> The FAQ does have the example of using ORDER BY LIMIT 1 for MAX(). What
> we don't have a workaround for is COUNT(*). I think that will require
> some cached value that obeys MVCC rules of visibility.
Note that that only handles min()/max() for the w
Christopher Browne <[EMAIL PROTECTED]> writes:
> Wouldn't this more or less be the same thing as having a trigger that
> does, upon each insert/delete "update pg_counts set count = count + 1
> where reltable = 45232;"? (... where 1 would be -1 for deletes, and where
> 45232 is the OID of the table
> A general query cache is something that is fairly clean and which might
> help both with count(*) and other queries.
>
> Many databases has a lot of tables that are more or less stable where this
> would work fine. From what I have heard mysql has something like this and
> it works well. For tabl
On Fri, 5 Sep 2003, Bruce Momjian wrote:
> > When I was curious as to how COUNT might be maintained, I was pretty
> > sure that this wouldn't be the preferred method...
>
> See my later idea of the trigger doing +/-1 rather than locking the
> value during the transaction.
>
> If we don't do it t
Christopher Browne wrote:
> Wouldn't this more or less be the same thing as having a trigger that
> does, upon each insert/delete "update pg_counts set count = count + 1
> where reltable = 45232;"? (... where 1 would be -1 for deletes, and where
> 45232 is the OID of the table...)
>
> Technically
Oops! [EMAIL PROTECTED] (Bruce Momjian) was seen spray-painting on a wall:
> Neil Conway wrote:
>> On Thu, 2003-09-04 at 22:02, Bruce Momjian wrote:
>> > My idea is that if a transaction doing a COUNT(*) would first look to
>> > see if there already was a visible cached value, and if not, it would
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> You could doubtless maintain a fairly good approximate total this
> >> way, and that would be highly useful for some applications ...
> >> but it isn't COUNT(*).
>
> > With MVCC allowing multiple rows with only on
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> You could doubtless maintain a fairly good approximate total this
>> way, and that would be highly useful for some applications ...
>> but it isn't COUNT(*).
> With MVCC allowing multiple rows with only one visible, I thought the
> INS
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Neil Conway wrote:
> >> In general, I don't think this is worth doing.
>
> > It is possible it isn't worth doing. Can the INSERT/DELETE
> > incrementing/decrementing the cached count work reliabily?
>
> I don't even see how the noti
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Neil Conway wrote:
>> In general, I don't think this is worth doing.
> It is possible it isn't worth doing. Can the INSERT/DELETE
> incrementing/decrementing the cached count work reliabily?
I don't even see how the notion of a single cached value make
Neil Conway wrote:
> On Thu, 2003-09-04 at 22:02, Bruce Momjian wrote:
> > My idea is that if a transaction doing a COUNT(*) would first look to
> > see if there already was a visible cached value, and if not, it would do
> > the COUNT(*) and insert into the cache table. Any INSERT/DELETE would
>
On Thu, 2003-09-04 at 22:02, Bruce Momjian wrote:
> My idea is that if a transaction doing a COUNT(*) would first look to
> see if there already was a visible cached value, and if not, it would do
> the COUNT(*) and insert into the cache table. Any INSERT/DELETE would
> remove the value from the c
Christopher Browne wrote:
> > IMHO portability is an important point. People are used to MAX() and
> > COUNT(*), and will be surprised that they need some special
> > treatment. While the reasons for this are perfectly explainable,
> > speeding up these aggregates with some extra effort would make
The world rejoiced as [EMAIL PROTECTED] (Andreas Pflug) wrote:
> Bruce Momjian wrote:
>
>>Greg Stark wrote:
>>
>>
>>>It has nothing to do with MVCC. It has to do with implementing this is hard in
>>>the general case.
>>>
>>>Think of examples like:
>>>
>>>select max(foo) group by bar;
>>>
>>>or
>>>
Bruce Momjian wrote:
Greg Stark wrote:
It has nothing to do with MVCC. It has to do with implementing this is hard in
the general case.
Think of examples like:
select max(foo) group by bar;
or
select max(foo) where xyz = z;
To do it properly max/min have to be special-cased and tightly inte
Greg Stark wrote:
> It has nothing to do with MVCC. It has to do with implementing this is hard in
> the general case.
>
> Think of examples like:
>
> select max(foo) group by bar;
>
> or
>
> select max(foo) where xyz = z;
>
> To do it properly max/min have to be special-cased and tightly inte
"Shridhar Daithankar" <[EMAIL PROTECTED]> writes:
> On 4 Sep 2003 at 11:32, Paulo Scardine wrote:
>
> > (Perhaps a newbie question, but I tried to google this out without success).
> >
> > Why postgres does an expensive seqscan to find the max(value) for an indexed
> > column? I think MAX() does
Hello
In my opinion, in 7.4 this optimized max() aggregate function would be a
very small, but significant improvement. As one of the members on the list
said, it would be a lot easier to port from/to other RDBMSes, with keeping
the same optimalization of the queries.
Bye,
Gergely Czuczy
mailto
This is an FAQ, BTW -- try searching the archives again. It's also
mentioned in the documentation:
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-aggregate.html
On Thu, 2003-09-04 at 11:10, Dennis Bjorklund wrote:
> On Thu, 4 Sep 2003, Shridhar Daithankar wrote:
> > It can not be cach
On Thu, 4 Sep 2003, Shridhar Daithankar wrote:
> > column? I think MAX() does not know or cares if a column is indexed, but...
>
> No. Postgresql uses MVCC which mean there could be multiple views of sample
> tuple active at the same time. There is no way to tell which is max. value for
> a col
On 4 Sep 2003 at 11:32, Paulo Scardine wrote:
> (Perhaps a newbie question, but I tried to google this out without success).
>
> Why postgres does an expensive seqscan to find the max(value) for an indexed
> column? I think MAX() does not know or cares if a column is indexed, but...
> Should not
24 matches
Mail list logo
