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

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

> however i got one problem (see below)... but when i started the psql
> monitor again,
> it did not appear. i'm worried that it would happen again.
>
> applieddata=> SELECT COUNT(*) FROM office, office_application
> applieddata-> WHERE office.code = office_application.office_code
> applieddata-> AND office_application.active
> applieddata-> AND NOT office.deleted;
> NOTICE: Message from PostgreSQL backend:
> The Postmaster has informed me that some other backend died
> abnormally and possibl
> y corrupted shared memory.
> I have rolled back the current transaction and am going to
> terminate your database
> system connection and exit.
> Please reconnect to the database system and repeat your query.
> pqFlush() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally before or
> while processing t
> he request.

This probably means another backend crashed while your connection was
going on. In general, I'd suggest removing -S and redirecting
STDOUT/STDERR to a file to get the log information. You can also
try looking in your database directory (<pgdata>/base/<database>)
for a core file.

>
> >
> >> 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,
> applieddata=> SELECT COUNT(*) FROM office, office_application
> applieddata-> WHERE office.code = office_application.office_code
> applieddata-> AND office_application.active
> applieddata-> AND NOT office.deleted;
> count
> -----
> 12534
>
> >and how many rows in office_application have active true
> applieddata=> SELECT COUNT(*) FROM office_application
> applieddata-> WHERE office_application.active;
> count
> -----
> 12973
>
> >and how many rows in office have deleted false?
> applieddata=> SELECT COUNT(*) FROM office
> applieddata-> WHERE NOT office.deleted;
> count
> -----
> 12534

Well, it's misguessing the number of rows badly for office, although
it'd probably only make it *more* likely to do a seq scan if it
though 12000 rows were going to match. I'm assuming that these
are the largest portion of these tables. I guess one thing to try
would be a vacuum analyze to see if that makes any difference.