From: Josh Berkus <josh@agliodbs.com>
To: denis@next2me.com
To: pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Wed, 9 Apr 2003 09:18:45 -0700
Message-ID: <200304090918.45275.josh@agliodbs.com> (raw)
In-Reply-To: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
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%'
Hash-aggregates may, in fact, help with this. Care to try downloading the
the source from CVS?
> 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 would be in keeping with MySQL's design
philosophy, which values performance and simplicity over accuracy and
precision -- the opposite of our philosophy.
> So, in the meantime, I've decided to split up my data into two sets,
> the static big tables which are handled by mysql, and the rest of it
> handled by postgresql....
Hey, if it works for you, it's probably easier than dealing with the
PostgreSQL workarounds to this performance issue. I'll ask you to give
PostgreSQL a try for those tables again when 7.4 comes out.
> ps: apologies for the cross-posting.
De nada. The Performance list is the right place for this sort of question
in the future.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, denis@next2me.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
In-Reply-To: <200304090918.45275.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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