Bruce Momjian wrote:
> > adb=> EXPLAIN SELECT COUNT(*) FROM atable WHERE atable.cfield = btable.cfield
> > AND atable.afield<10;
> > NOTICE: QUERY PLAN:
>
> But your only restriction is < 10. That is not enough. Make it = 10,
> and I think it will use the index.
Ok. We did it! :)
-------------
adb=> EXPLAIN SELECT COUNT(*) FROM atable WHERE atable.cfield = btable.cfield
AND atable.afield=10;
NOTICE: QUERY PLAN:
Aggregate (cost=4.10 rows=1 width=12)
-> Nested Loop (cost=4.10 rows=1 width=12)
-> Index Scan using aindex on atable (cost=2.05 rows=1 width=8)
-> Index Scan using hindex on btable (cost=2.05 rows=10000 width=4)
-------------
But look here:
-------------
adb=> EXPLAIN SELECT * FROM atable WHERE atable.cfield = btable.cfield AND
atable.afield IN (SELECT btable.bfield WHERE btable.bfield=10);
NOTICE: QUERY PLAN:
Hash Join (cost=1483.00 rows=10000 width=24)
-> Seq Scan on atable (cost=399.00 rows=10000 width=20)
SubPlan
-> Index Scan using gindex on btable (cost=2.05 rows=1 width=4)
-> Hash (cost=399.00 rows=10000 width=4)
-> Seq Scan on btable (cost=399.00 rows=10000 width=4)
-------------
This is the same dumbness again. Will you fix the optimizer?
And more: would you make a cool data type, a reference, which
is a physical record number of a foreign record? This could make certain
type of joins VERY fast, too good to be true. Such thing is really
an incorporation of elements of networking (networked? :) data
model into relational model.
--
Leon.
