Re: [GENERAL] Fast join

Patrick Welche wrote:
>
> > adb=> create table atable (afield int4 primary key,bfield int4);
> > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'atable_pkey' for t
> > able 'atable'
> > CREATE
> > adb=> create table btable (afield int4 primary key,bfield int4);
> > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'btable_pkey' for t
> > able 'btable'
> > CREATE
> > adb=> create index aindex on atable (bfield);
> > CREATE
> > adb=> create index bindex on btable (bfield);
> > CREATE
>
> *** Try a VACUUM ANALYZE; here ***

To all who will suggest vacuum analyse: that is exactly the thing
which is done after filling the database with dummy values. It doesn't
help anything. Here is the piece of code:

------------------
ExecQuery(conn,"CREATE TABLE atable (afield int4, bfield int4, cfield int4,
dfield int4, efield int4)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE TABLE btable (afield int4, bfield int4, cfield int4,
dfield int4, efield int4)",PGRES_COMMAND_OK);
ExecQuery(conn,"BEGIN",PGRES_COMMAND_OK);

t1=time(NULL);
printf("inserting values...\n");
for (i = 0; i < 10000; i++)
{
sprintf(buf,"INSERT INTO atable values (%d, %d, %d, %d, %d)",i,i,i,i,i);
ExecQuery(conn,buf,PGRES_COMMAND_OK);
sprintf(buf,"INSERT INTO btable values (%d, %d, %d, %d, %d)",i,i,i,i,i);
ExecQuery(conn,buf,PGRES_COMMAND_OK);
}
t2=time(NULL);
printf("time elapsed is %e \n",difftime(t2,t1));
ExecQuery(conn,"COMMIT",PGRES_COMMAND_OK);

printf("trying to create index...\n");
t1=time(NULL);
ExecQuery(conn,"CREATE INDEX aindex ON atable (afield)",PGRES_COMMAND_OK);
t2=time(NULL);
printf("time elapsed is %e \n",difftime(t2,t1));

ExecQuery(conn,"CREATE INDEX bindex ON atable (bfield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX cindex ON atable (cfield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX dindex ON atable (dfield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX eindex ON atable (efield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX findex ON btable (afield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX gindex ON btable (bfield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX hindex ON btable (cfield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX iindex ON btable (dfield)",PGRES_COMMAND_OK);
ExecQuery(conn,"CREATE INDEX jindex ON btable (efield)",PGRES_COMMAND_OK);
printf("trying to commit transaction...\n");
// ExecQuery(conn,"COMMIT",PGRES_COMMAND_OK);

printf("opening SELECT transaction...\n");
ExecQuery(conn,"BEGIN",PGRES_COMMAND_OK);
ExecQuery(conn,"VACUUM ANALYZE",PGRES_COMMAND_OK);

printf("closing fetch transaction...\n");
ExecQuery(conn,"COMMIT",PGRES_COMMAND_OK);

------------------

Then here is EXPLAIN:

------------------
adb=> EXPLAIN SELECT COUNT(*) FROM atable WHERE atable.cfield = btable.cfield
AND atable.afield>100;
NOTICE: QUERY PLAN:

Aggregate (cost=1047.69 rows=3334 width=12)
-> Hash Join (cost=1047.69 rows=3334 width=12)
-> Seq Scan on btable (cost=399.00 rows=10000 width=4)
-> Hash (cost=198.67 rows=3334 width=8)
-> Index Scan using aindex on atable (cost=198.67 rows=3334
width=8)
------------------

--
Leon.