[SQL] primary key scans in sequence

[bernd]

hey i have the following table def (834.000 rows, vaccum analyze'd):
dl_online=# \d mitglied
  Table "mitglied"
   Attribute| Type |  Modifier
+--+
 mitgliedid | bigint   | not null
 dlnummer   | varchar(30)  |
 vorname| varchar(50)  |
 zuname | varchar(50)  | not null
 geburtsdatum   | varchar(20)  |
 aktiv  | boolean  | not null default 't'::bool
 strasse| varchar(255) |
 plz| varchar(25)  |
 ort| varchar(255) |
 telefon| varchar(255) |
 eintrittsdatum | varchar(20)  |
 geschlechtid   | bigint   | not null default 3
 treuelevelid   | bigint   | not null default 1
 clubmitglied   | boolean  | not null default 'f'::bool
 bemerkungen| text |
 mid| bigint   |

Indices: mitglied_dlnummer_idx, [on dlnummer]
 mitglied_pkey   [on mitgliedid]

ok;  i use 2 querys:

1) get one row over dlnummer:
 dl_online=# explain select * from mitglied where dlnummer = '098765432';
 NOTICE:  QUERY PLAN:
 Index Scan using mitglied_dlnummer_idx on mitglied  (cost=0.00..4.77 rows=1
width=154)
 EXPLAIN

2) get one row over the primatry key (mitgliedid):
dl_online=# explain select * from mitglied where mitgliedid = 833228;
NOTICE:  QUERY PLAN:
Seq Scan on mitglied  (cost=0.00..18674.74 rows=1 width=154)
EXPLAIN

why doesn't use postrges in (2) the primary-key-index?? take a look at the
cost! and both queries returns only ONE row (the optimizer knows that fact).

and the worst. in(2) the query take ~3sec. in this time the cpu works on
99.9% (rh-linux 7 on a compac dc10 -alpha).
the other works fine (no time to calculate, "no" use of the cpu!).

PS: i tried also "set ENABLE_SEQSCAN to OFF". no chance, (2) will work with
Seq Scan.

thx to ANY hint!!
bernd.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] LOG: unexpected EOF on client connection

[Bernd Nawothnig]

Hello,

A client application written in C# + npgsql 2.0.6 causes reproducable
log entries like this:

LOG:  unexpected EOF on client connection
LOG:  could not receive data from client: Die Verbindung wurde vom
Kommunikationspartner zurückgesetzt(*)

(*) Connection reset by peer

The responsable Programmer claims autovacuum caused the malfunction.
But after disabling autovacuum the same error occured. Another
program, written Python + psycopg2, which invokes exactly the same
function resulting the above connection reset in the C# program runs
without any problem while inserting far more than 10 times the amount
of data the C# program should do. Even running 2 parallel instances of
the Python program does not result in any error.


Server is version 8.4 + Postgis 1.4 (both compiled with gcc 4.3), OS
is Debian Lenny running on a Quadcore-Xeon Dell Precision 690 with 2
GB RAM. When the above error occurs the C# program is the only client
connecting to the server.


What may be the most probable cause of that?



Bernd

-- 
No time toulouse

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql