I also have seen this slowdown if you join more then 3 or 4 tables. I have
the impression that the query analyzer just linearly searches through a
number of tables in stead of add a first selects to these tables?
>
>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];
>
>
>
>
>
