pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
strange query execution times
3+ messages / 2 participants
[nested] [flat]

* strange query execution times
@ 2001-10-01 17:52  Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Markus Bertheau @ 2001-10-01 17:52 UTC (permalink / raw)
  To: 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 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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: strange query execution times
@ 2001-10-02 15:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2001-10-02 15:49 UTC (permalink / raw)
  To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql

Markus Bertheau <twanger@bluetwanger.de> writes:
> 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.

How many rows actually satisfy "rka.a_id = 620 and rka.beziehung = 3"?

The issue appears to be that the planner estimates one matching row
in the one case and two matching rows in the second.  Given the estimate
of one row, it decides to go for the low-overhead nested loop plan.
I am guessing that there are really considerably more than two matching
rows, and so the nested loop plan loses badly compared to the mergejoin,
which takes longer to set up but is better able to cope with many rows.

FWIW, 7.2 has better statistics and should be better able to pick the
right plan in this context ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: strange query execution times
@ 2001-10-05 06:48  Markus Bertheau <twanger@bluetwanger.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Markus Bertheau @ 2001-10-05 06:48 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

On Tue, 2001-10-02 at 17:49, Tom Lane wrote:
> Markus Bertheau <twanger@bluetwanger.de> writes:
> > 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.
> 
> How many rows actually satisfy "rka.a_id = 620 and rka.beziehung = 3"?
> 
> The issue appears to be that the planner estimates one matching row
> in the one case and two matching rows in the second.  Given the estimate
> of one row, it decides to go for the low-overhead nested loop plan.
> I am guessing that there are really considerably more than two matching
> rows, and so the nested loop plan loses badly compared to the mergejoin,
> which takes longer to set up but is better able to cope with many rows.
> 
> FWIW, 7.2 has better statistics and should be better able to pick the
> right plan in this context ...

13 rows do. Is there a way to force 7.0.3 (or 7.1.3) to use the
mergejoin with the straightforward condition?

Markus Bertheau
Cenes Data GmbH





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-10-05 06:48 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-01 17:52 strange query execution times Markus Bertheau <twanger@bluetwanger.de>
2001-10-02 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-05 06:48   ` Markus Bertheau <twanger@bluetwanger.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox