Fast join - Mailing list pgsql-general

From Leon
Subject Fast join
Date
Msg-id 37789F8D.D5080985@udmnet.ru
Whole thread Raw
Responses Re: [GENERAL] Fast join
List pgsql-general
Hello all!

I assume this is a developer's list, isn't it? Because the
address hackers-request@postgreSQL.org mentioned in PostgreSQL FAQ
doesn't work.

Ok. Let me begin with the beginning. We are a group of developers
of an free accounting system here in Russia. We want to use Postgres
as our database server. It is cool, but we encountered a problem
during preliminary tests, a problem concerning joins. The problem
is that joins are too slow. Consider that sequence of commands
given at the command prompt of psql (strange duplicates of NOTICEs are
removed):

--------------------
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
adb=> explain select * from atable where atable.afield=btable.afield and atable.
bfield>10;
NOTICE:  QUERY PLAN:

Hash Join  (cost=109.69 rows=335 width=12)
  ->  Seq Scan on btable  (cost=43.00 rows=1000 width=4)
  ->  Hash  (cost=21.67 rows=334 width=8)
        ->  Index Scan using aindex on atable  (cost=21.67 rows=334 width=8)
-------------------

This shows clearly that Postgres doesn't do any attempt to use
existing indices and restrictive qualifications doing join. That's
unacceptable to us, because it slows join immensely.

What is desired here is logic of a networked (or networking? :)
data model as opposed to general relational model, because logic
of nets is simpler than relational logic and thus can be done
faster. They say that future standard of SQL will incorporate
such data types as links (references), so how soon will it appear
in Postgres? We need this feature URGENTLY, because accounting
data is mostly network-type,rather than general relational-type.
Please reply me.

--
Leon.


pgsql-general by date:

Previous
From: Herouth Maoz
Date:
Subject: Re: [GENERAL] Re: Data warehousing
Next
From: Leon
Date:
Subject: Re: [GENERAL] Fast join