Thread: Select slow over network

Hello. I'm using PostgreSQL 8.1.4 with WinXP Sp2.
The acess over network 100mbit is too slow.
My query take 100 times more.
The use of CPU and network is low,  less than  1%.
Searching in the web I found other with the same problem, but no reply to resolve this problem.
The query is a simple "SELECT * FROM table".
the EXPLAIN of the query:
    Seq Scan on cliente_geral  (cost=0.00..344.95 rows=6795 width=587)

Thanks.

am  Fri, dem 17.11.2006, um 17:22:47 -0200 mailte Leodinei Bielak folgendes:
> Hello. I'm using PostgreSQL 8.1.4 with WinXP Sp2.
> The acess over network 100mbit is too slow.
> My query take 100 times more.

How long is a long time?


> The use of CPU and network is low,  less than  1%.
> Searching in the web I found other with the same problem, but no reply to
> resolve this problem.
> The query is a simple "SELECT * FROM table".

A dump query, sorry.


> the EXPLAIN of the query:
>     Seq Scan on cliente_geral  (cost=0.00..344.95 rows=6795 width=587)

Can you give as the output from 'EXPLAIN ANALYSE', so we can see not
only the estimate cost? How long is the real result? Perhaps, you have
wrong statistics, perhaps. And: without any WHERE - Conditions, every
SELECT-Query ends in a 'Seq Scan', and this will be slow. What do you
expected?


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

I'm finding a strange sort of 'zombie' table behavior.
I try to delete a row of a database that used to be referenced by a
table that has now been deleted.

I do
delete from staging_deal where staging_deal_id = 1
and get
ERROR:  update or delete on "staging_deal" violates foreign key
constraint "staging_deal_fkey" on "staging_document"
DETAIL:  Key (staging_deal_id)=(1) is still referenced from table
"staging_document".

I try to find that table
select * from staging_document
but
ERROR:  relation "staging_document" does not exist

However pg_class does know that table
select * from pg_class where relname = 'staging_document'
gives me a row

The version is
"PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3"

I've tried reindexing pg_class, but the issue hasn't been solved.
Restarting Postgres doesn't help either.
On thing I tried in a test deployment was to delete the row from
pg_class, but I get
ERROR:  could not open relation with OID 22427646
type errors, and it seems to leave me worse off.

Any ideas?

Thank you
Jaime


***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice.  You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of:  (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************

In case anybody was thinking of spending time on this:

The problem was the existence of a table of the same name in a different
schema.

ERROR:  update or delete on "staging_deal" violates foreign key
constraint "staging_deal_fkey" on "staging_document"

should have in fact pointed to "doclib.staging_document"

Silly mistake, but it seems that ERROR messages don't specify the schema
of tables. It would be a time-saver to show them.

Thanks


Jaime Silvela wrote:
> I'm finding a strange sort of 'zombie' table behavior.
> I try to delete a row of a database that used to be referenced by a
> table that has now been deleted.
>
> I do
> delete from staging_deal where staging_deal_id = 1
> and get
> ERROR:  update or delete on "staging_deal" violates foreign key
> constraint "staging_deal_fkey" on "staging_document"
> DETAIL:  Key (staging_deal_id)=(1) is still referenced from table
> "staging_document".
>
> I try to find that table
> select * from staging_document
> but
> ERROR:  relation "staging_document" does not exist
>
> However pg_class does know that table
> select * from pg_class where relname = 'staging_document'
> gives me a row
>
> The version is
> "PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3"
>
> I've tried reindexing pg_class, but the issue hasn't been solved.
> Restarting Postgres doesn't help either.
> On thing I tried in a test deployment was to delete the row from
> pg_class, but I get
> ERROR:  could not open relation with OID 22427646
> type errors, and it seems to leave me worse off.
>
> Any ideas?
>
> Thank you
> Jaime
>
>
> ***********************************************************************
> Bear Stearns is not responsible for any recommendation, solicitation,
> offer or agreement or any information about any transaction, customer
> account or account activity contained in this communication.
>
> Bear Stearns does not provide tax, legal or accounting advice.  You
> should consult your own tax, legal and accounting advisors before
> engaging in any transaction. In order for Bear Stearns to comply with
> Internal Revenue Service Circular 230 (if applicable), you are notified
> that any discussion of U.S. federal tax issues contained or referred to
> herein is not intended or written to be used, and cannot be used, for
> the purpose of:  (A) avoiding penalties that may be imposed under the
> Internal Revenue Code; nor (B) promoting, marketing or recommending to
> another party any transaction or matter addressed herein.
> ***********************************************************************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>


***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice.  You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of:  (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************

Jaime Silvela <JSilvela@Bear.com> writes:
> I do
> delete from staging_deal where staging_deal_id = 1
> and get
> ERROR:  update or delete on "staging_deal" violates foreign key
> constraint "staging_deal_fkey" on "staging_document"
> DETAIL:  Key (staging_deal_id)=(1) is still referenced from table
> "staging_document".

> I try to find that table
> select * from staging_document
> but
> ERROR:  relation "staging_document" does not exist

Sounds to me like "staging_document" is in a schema that is not in your
search path?

            regards, tom lane