Обсуждение: Re(2): db server is too slow, real slow

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

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.


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)

sszabo@megazone23.bigpanda.com writes:
>
>We'll probably need some additional information:
>
>What settings did you start the server with, what queries are you running
>that are slow (with schema, index information), what does explain show for
>the queries?
>
>Stephan Szabo
>sszabo@bigpanda.com
>
>On Thu, 19 Oct 2000, pgsql-admin wrote:
>
>> I'm using
>> PostgreSQL 6.5.3 on i686-pc-linux-gnu.
>> I have a backup of our database (sql dump and large object)
>> backup.tar.bz2 with the size of 10121985.
>> We have users accessing our database from http and
>> odbc (ms excel).
>>
>> Can someone pls tell how to solve this problem?
>>
>> Thank you very much.
>> Sherwin
>>
>> Here are the processes:
>> 6:08pm  up 12 days, 23:39,  2 users,  load average: 12.99, 12.70, 11.29
>> 74 processes: 57 sleeping, 17 running, 0 zombie, 0 stopped
>> CPU states: 24.9% user, 74.2% system,  0.0% nice,  0.7% idle
>> Mem:   387032K av,  184556K used,  202476K free,   48872K shrd,   49308K
>> buff
>> Swap:  265032K av,    5284K used,  259748K free                   73596K
>> cached
>>
>>   PID USER     PRI  NI  SIZE  RSS SHARE STAT  LIB %CPU %MEM   TIME
>COMMAND
>> 11345 postgres   9   0  2604 2544  1928 R       0 13.6  0.6   1:23
>> postmaster
>> 11220 postgres   9   0  2604 2544  1924 R       0 13.4  0.6   3:10
>> postmaster
>> 11213 postgres   7   0  2604 2544  1928 R       0 13.0  0.6   3:16
>> postmaster
>> 10860 postgres   6   0  5552 5492  2068 R       0 12.8  1.4  14:48
>> postmaster
>> 10862 postgres   8   0  2588 2528  1896 R       0 12.8  0.6  14:17
>> postmaster
>> 10996 postgres   8   0  4404 4344  2072 R       0 12.8  1.1   8:28
>> postmaster
>> 11168 postgres   6   0  3552 3492  2072 R       0 12.8  0.9   3:51
>> postmaster
>> 10966 postgres   5   0  4652 4592  2064 R       0 12.6  1.1  10:04
>> postmaster
>> 10957 postgres   7   0  4800 4740  2064 R       0 12.4  1.2  10:51
>> postmaster
>> 10861 postgres   5   0  5396 5336  2064 R       0 12.2  1.3  14:15
>> postmaster
>> 10867 postgres   9   0  5332 5272  2060 R       0 12.2  1.3  13:53
>> postmaster
>> 10892 postgres   5   0  5084 5024  2060 R       0 11.8  1.2  12:33
>> postmaster
>> 10995 postgres   5   0  4416 4356  2072 R       0 11.8  1.1   8:33
>> postmaster
>> 11163 postgres   5   0  2612 2552  1924 R       0 11.4  0.6   4:01
>> postmaster
>> 11170 postgres   5   0  2428 2368  1844 R       0 10.6  0.6   1:07
>> postmaster
>> 10888 postgres   5   0  2584 2524  1896 R       0 10.2  0.6  12:47
>> postmaster
>>
>

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?