Skip site navigation (1) Skip section navigation (2)

BUG #3033: performance issue with self join

From: "Luigi Tarenga" <luigi(dot)tarenga(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3033: performance issue with self join
Date: 2007-02-19 20:27:32
Message-ID: 200702192027.l1JKRWWk029032@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3033
Logged by:          Luigi Tarenga
Email address:      luigi(dot)tarenga(at)gmail(dot)com
PostgreSQL version: 8.1.4
Operating system:   OpenBSD 4.0 amd64
Description:        performance issue with self join
Details: 

hi, 
my full version is PostgreSQL 8.1.4 on x86_64-unknown-openbsd4.0, compiled
by GCC cc (GCC) 3.3.5 (propolice)
installed by the OpenBSD port.
i'm not sure this is a bug but i found a strange
behavior during some custom benchmark.
i'm used to run a query like this one on a 14 row table:
select count(*)
from emp7 emp1,
     emp7 emp2,
     emp7 emp3,
     emp7 emp4,
     emp7 emp5,
     emp7 emp6,
     emp7 emp7_7
where emp7_7.deptno=10;

it should return:
   count
-----------
 105413504
(1 row)

and the first time it takes about 30 seconds on my pc.
the problem raise when i make lots of insert in the
same table and then delete new rows until i left the
original 14. if i rerun the select it seems to block
forever (i waited more then 10 minutes).

if i drop the table, recreate it with 14 rows and
rerun the select i can get the result in 30 seconds again.

i have a script to automatize all this procedure.
i use it this way:
./emp7.sh create
./emp7.sh run     #run the select and exit in 30 sec.
./emp7.sh ins20k  #run a 10k insert test
./emp7.sh run     #now it blocks forever (almost)!
./emp7.sh drop    #drop the table, you can restart the procedure from the
"create"
the script code:

#!/bin/sh

if [ "$1" = "create" ]; then
psql postgres <<EOF
create table emp7 (
  empno        int        not null,
  ename        char(10),
  job          char(9),
  mgr          int,
  hiredate     date,
  sal          float,
  comm         float,
  deptno       int
 );
create unique index pkemp7 on emp7(empno);
insert into emp7 (empno, ename, deptno) values(1,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(2,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(3,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(4,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(5,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(6,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(7,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(8,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(9,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(10,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(11,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(12,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(13,'SMITH',10);
insert into emp7 (empno, ename, deptno) values(14,'SMITH',10);
EOF
fi

if [ "$1" = "drop" ]; then
psql postgres <<EOF
DROP TABLE emp7 ;
EOF
fi

if [ "$1" = "run" ]; then
echo
echo ... benchmark query in corso ...
time psql postgres <<EOF
select count(*)
from emp7 emp1,
     emp7 emp2,
     emp7 emp3,
     emp7 emp4,
     emp7 emp5,
     emp7 emp6,
     emp7 emp7_7
where emp7_7.deptno=10;
EOF
fi

if [ "$1" = "ins10k" ]; then
cont=10000
{
  while [ cont -lt 20000 ] ; do
    echo "insert into emp7 (empno, ename, deptno)
values($cont,'SMITH2',10);"
    cont=$(($cont+1))
  done
}| time psql postgres > /dev/null

psql postgres <<EOF
delete from emp7 where ename = 'SMITH2';
EOF
fi

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2007-02-19 20:46:38
Subject: Re: BUG #2973: Compile Error with MIPSpro compiler
Previous:From: Craig WhiteDate: 2007-02-19 19:30:00
Subject: BUG #3032: Commit hung for days

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group