Re: [GENERAL] Fast join
От | Leon |
---|---|
Тема | Re: [GENERAL] Fast join |
Дата | |
Msg-id | 3778C9B4.77909CEA@udmnet.ru обсуждение исходный текст |
Список | 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.
В списке pgsql-general по дате отправления: