The postgres query sounds slower than I would expect. Have you run a "VACUUM
ANALYZE"? This is needed initially, and occasionally, to compute statistics for
optimal query planning. VACUUM also recovers disk space. "man vacuum"
You may want to prefix your SELECT statement with EXPLAIN to display the query
plan. Try this before and after the VACUUM.
As far as the count(*) goes, I am not familiar with MySQL's implementation, but
postgres does a sequential scan on a count(*) without a WHERE condition. Some
SQL engines have this value saved off for just this kind of query. This is just
one of the many performance/spaces tradeoffs. Postgres, you will find, has many
other redeeming features.
Howie wrote:
> ive been evaluating pgsql as a replacement for MySQL, which currently
> handles all of a client's authentication needs for their websites.
> however, some queries that have been blindingly fast under MySQL are
> incredlbly slow under pgsql.
>
> for instance:
>
> ---[ CUT ]---
> SELECT
> doms.dom, types.batch, types.active, codes.code
> FROM
> doms,types,codes,hosts
> WHERE
> hosts.client=doms.client AND
> doms.client=types.client AND
> types.batch='FREECODE' AND
> types.type=codes.type AND
> hosts.hostname='somehostnamehere.com';
> ---[ CUT ]---
>
> under MySQL, this query takes about 2-3 seconds. under pgsql v6.3, this
> query takes roughly 40 seconds to a minute. system is a P133, Linux
> kernel 2.0.33, 128m mem, EIDE based ( bah ) disks. there is very little
> going on while executing these queries since this is a development box.
>
> there are keys/indexes on hosts.client (primary), doms.client (primary),
> types.batch & types.client (unique index), and types.type (primary). all
> the 'client' columns are int4, types.batch is "char(8) not null". the
> pgsql schema and mysql schema are the same and the indexes/keys are the
> same.
>
> doms has about 80 rows as does hosts. types has ~350 rows, codes has 157k
> rows ( a "select count(*) from codes" takes about 3 seconds to return;
> MySQL returns _immediately_ ).
>
> am i missing something or is pgsql really that much slower ? if you need
> the table layout and some sample data i can supply that...
>
> ---
> Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
> [[NSNotificationCenter defaultCenter] addObserver:systemAdministrator
> selector:@selector(disableUserAccount:) name:@"UserIsWhining" object:aLuser];
