strange query execution times - Mailing list pgsql-sql

Hi guys,

cenes_test=# select version();                           version                            
---------------------------------------------------------------PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc
2.95.2
(1 row)

cenes_test=# \d personen                   Table "personen"   Attribute    |     Type      |       Modifier       
-----------------+---------------+----------------------personen_id     | integer       | not nulllogin           |
char(10)     | not nullpasswort        | char(32)      | not nulldeaktiviert     | smallint      | not null default
'0'firma          | char(60)      | nachname        | varchar(60)   | not nullvorname         | varchar(60)   | not
nulltelefon        | varchar(50)   | telefax         | varchar(50)   | email           | varchar(80)   | not
nulluse_perm       | smallint      | not null default '0'titel           | varchar(20)   | mobiltelefon    |
varchar(50)  | abteilung       | varchar(60)   | funktion        | varchar(60)   | erfass_datum    | timestamp     |
notnullzeitstempel     | timestamp     | not nullkreditlimit     | numeric(11,5) | bild            | varchar(100)  |
anbieter_tpl   | varchar(100)  | firma_2         | varchar(255)  | url             | varchar(100)  | hrb             |
varchar(100) | crefo           | varchar(100)  | ssl_client_s_dn | varchar(255)  | 
 
Indices: erfass_datum_personen_key,        personen_login_key,        personen_pkey
Constraints: ((use_perm = 0::int2) OR (use_perm = 1::int2))            ((deaktiviert = 0::int2) OR (deaktiviert =
1::int2))

cenes_test=# \d r_kunden_anbieter   Table "r_kunden_anbieter"Attribute |   Type   | Modifier 
-----------+----------+----------k_id      | bigint   | not nulla_id      | bigint   | not nullbeziehung | smallint |
notnull
 
Indices: beziehung_r_kunden_anbieter_key,        r_kunden_anbieter_a_id_key,        r_kunden_anbieter_k_id_key

cenes_test=# explain select p.* , rka.beziehung from personen p join
r_kunden_anbieter rka on p.personen_id = rka.k_id where rka.a_id = 620
and rka.beziehung != 0 and rka.beziehung != 2 and rka.beziehung != 1 and
rka.beziehung != 4  order by erfass_datum;
NOTICE:  QUERY PLAN:

Sort  (cost=59.88..59.88 rows=2 width=274) ->  Merge Join  (cost=53.79..59.87 rows=2 width=274)       ->  Sort
(cost=11.37..11.37rows=2 width=10)             ->  Seq Scan on r_kunden_anbieter rka  (cost=0.00..11.36
 
rows=2 width=10)       ->  Sort  (cost=42.42..42.42 rows=484 width=264)             ->  Seq Scan on personen p
(cost=0.00..20.84rows=484
 
width=264)

EXPLAIN
cenes_test=# explain select p.* , rka.beziehung from personen p join
r_kunden_anbieter rka on p.personen_id = rka.k_id where rka.a_id = 620
and rka.beziehung = 3  order by erfass_datum;
NOTICE:  QUERY PLAN:

Sort  (cost=35.80..35.80 rows=1 width=274) ->  Nested Loop  (cost=0.00..35.80 rows=1 width=274)       ->  Seq Scan on
r_kunden_anbieterrka  (cost=0.00..8.90 rows=1
 
width=10)       ->  Seq Scan on personen p  (cost=0.00..20.84 rows=484
width=264)

EXPLAIN
cenes_test=# 

table personen holds not only customers but also suppliers. table
r_kunden_anbieter describes the relationship between customers and
suppliers. there are five status, 0 to 4 in attribute beziehung. both
queries return the same results. they select all customers which have a
certain relationship (beziehung = 3) to a given supplier.
personen has 484 rows, r_kunden_anbieter 327.
the database is freshly vacuum analyzed.
The first query takes 0.038 sec, the second 0.879 secs. Why is the
negotiation of all values except the one we are looking for faster than
to look for equality of the one we are looking for?

Markus Bertheau  & Horst Schwarz

Cenes Data GmbH