Re: [SQL] Yet Another (Simple) Case of Index not used

Поиск
Список
Период
Сортировка
От Kevin Brown
Тема Re: [SQL] Yet Another (Simple) Case of Index not used
Дата
Msg-id 20030419130146.GI1847@filer
обсуждение исходный текст
Ответ на Re: [SQL] Yet Another (Simple) Case of Index not used  (Josh Berkus <josh@agliodbs.com>)
Ответы Re: [SQL] Yet Another (Simple) Case of Index not used  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Josh Berkus wrote:
> Denis,
>
> > Are you saying the 7.4 'group by' trick would be faster than the simple
> > select count(*)? That seems hard to believe, being that the request now has
> > to fetch / sort the data. I must be missing something.
>
> No, I'm saying that the 7.4 hash-aggregate is faster than the same query was
> under 7.2 or 7.3.   Much faster.   But it does little to speed up a raw
> count(*).
>
> > The kind of requests that I am really interested in are:
> > select count(*) from table where table.column like 'pattern%'
>
> > These seems to go much master on mysql (which I guess it not a MVCC
> > database? or wasn't the Innobase supposed to make it so?),
>
> They did incorporate a lot of MVCC logic into InnoDB tables, yes.
> Which means that if SELECT count(*) on an InnoDB table is just as
> fast as a MyISAM table, then it is not accurate.

This is not necessarily true.  The trigger-based approach to tracking
the current number of rows in a table might well be implemented
internally, and that may actually be much faster than doing it using
triggers (the performance losses you saw may well have been the result
of PG's somewhat poor trigger performance, and not the result of the
approach itself.  It would be interesting to know how triggers effect
the performance of other databases).


--
Kevin Brown                          kevin@sysexperts.com


В списке pgsql-performance по дате отправления:

Предыдущее
От: Victor Danilchenko
Дата:
Сообщение: Re: Query speed problems
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [SQL] Yet Another (Simple) Case of Index not used