Re: Re(2): db server is too slow, real slow

On Fri, 20 Oct 2000, pgsql-admin wrote:

> The postgres server was started with the command:
> /usr/bin/postmaster -i -S -D/var/lib/pgsql

Have you tried changing the values of -B to see if increasing the
number of buffers helps?

> Here is just one query executed from Embperl pages with Perl DBI:
> officedata=> explain SELECT * FROM office, office_application
> officedata-> WHERE office.code = office_application.office_code
> officedata-> AND office_application.active
> officedata-> AND NOT office.deleted;
> NOTICE:  QUERY PLAN:
>
> Hash Join  (cost=5524.86 rows=644 width=262)
>   ->  Seq Scan on office_application  (cost=4261.49 rows=9767 width=245)
>   ->  Hash  (cost=908.88 rows=854 width=17)
>         ->  Seq Scan on office  (cost=908.88 rows=854 width=17)
>
> note: office.code and office_application.office_code are serial data types.
>          office_application.active and office.deleted are boolean.
>          others are char, inet, oid (for lobj), and date data types.

How many rows does the query actually give you, and how many rows in
office_application have active true and how many rows in office have
deleted false?
I don't know too much about this, but with the way the estimates are done,
I'm not sure that an index will actually end up helping you even if you're
selecting the lesser of the non-NULL values.

> Here is another query executed from ms excel (pgsql-odbc):
> workdata=> explain select * from consol;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on consol  (cost=1133.48 rows=8560 width=256)
>
> note: consol table contains no index, no sequence, no primary key
>          (just char, date, money, and inet)

Well the plan looks reasonable, sequence scan is probably the
fastest way to get the entire table unordered.
How long is it taking to do this query via odbc and from psql on
the local machine?