Re: [GENERAL] Fast join - Mailing list pgsql-general

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.