Received: from leela.one-2-one.net (leela.one-2-one.net [217.115.142.65]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f91Hcdn92131 for ; Mon, 1 Oct 2001 13:38:40 -0400 (EDT) (envelope-from twanger@bluetwanger.de) Received: from [192.168.0.14] (pD9501217.dip.t-dialin.net [217.80.18.23]) by leela.one-2-one.net (8.11.0/8.11.0) with ESMTP id f91Hfed25212 for ; Mon, 1 Oct 2001 19:41:40 +0200 Subject: strange query execution times From: Markus Bertheau To: postgres sql list Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/0.14 (Preview Release) Date: 01 Oct 2001 19:52:23 +0200 Message-Id: <1001958754.2485.31.camel@entwicklung01.cenes.de> Mime-Version: 1.0 X-Archive-Number: 200110/12 X-Sequence-Number: 4967 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 null login | char(10) | not null passwort | char(32) | not null deaktiviert | smallint | not null default '0' firma | char(60) | nachname | varchar(60) | not null vorname | varchar(60) | not null telefon | varchar(50) | telefax | varchar(50) | email | varchar(80) | not null use_perm | smallint | not null default '0' titel | varchar(20) | mobiltelefon | varchar(50) | abteilung | varchar(60) | funktion | varchar(60) | erfass_datum | timestamp | not null zeitstempel | timestamp | not null kreditlimit | 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 null a_id | bigint | not null beziehung | smallint | not null 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.37 rows=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.84 rows=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_anbieter rka (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