Re: usage of indexes for inner joins

Sequence scans of an empty table are going to be faster than an index scan,
so the database uses the sequence scan. Put some data in the tables (some
thousands or millions of records) and then see if it uses an index scan.

Ben

""Jan Theodore Galkowski"" <bayesianlogic(at)acm(dot)org> wrote in message
news:1190954508(dot)31020(dot)1213039025(at)webmail(dot)messagingengine(dot)com(dot)(dot)(dot)
>I fear this has been asked many times about PostgreSQL, and I have read
> the docs about how indexes are supposed to be defined and used, but I
> don't understand why the engine and optimizer is doing what it does in
> the simplest of situations. Is it that its tuning is heavily data
> dependent?
>
> My case of interest is more complicated, but I decided to create a toy
> case to try to understand. Here it is:
>
>
> -- Table "foo" DDL
>
> CREATE TABLE "public"."foo"(
>
> "projectid" int4 NOT NULL ,
>
> "uid" int4 NOT NULL ,
>
> "name" varchar(254) NOT NULL ,
>
> "ver" varchar(127) NOT NULL ,
>
> "startdate" date NOT NULL ,
>
> "enddate" date NOT NULL ,
>
> "status" varchar(254) NOT NULL ,
>
> "percentdone" numeric(7,2) NOT NULL ,
>
> "championuid" int4 NOT NULL ,
>
> "pmuid" int4 NOT NULL ,
>
> PRIMARY KEY ("projectid")
>
> ) WITHOUT OIDS;
>
>
> -- Table "bignum" DDL
>
> CREATE TABLE "public"."bignum"(
>
> "thing" numeric(100) NOT NULL
>
> ) WITHOUT OIDS;
>
> CREATE INDEX "t" ON "public"."bignum" USING btree ("thing");
>
>
> Running
>
> EXPLAIN ANALYZE SELECT A.* FROM bignum B, foo A WHERE A.projectid
> = B.thing;
>
> yields:
>
> Nested Loop (cost=0.00..15.51 rows=1 width=407) (actual
> time=0.041..0.041 rows=0 loops=1)
>
> Join Filter: ((a.projectid)::numeric = b.thing) ->
>
> Seq Scan on bignum b (cost=0.00..1.01 rows=1 width=16) (actual
> time=0.024..0.027 rows=1 loops=1) ->
>
> Seq Scan on foo a (cost=0.00..11.80 rows=180 width=407) (actual
> time=0.003..0.003 rows=0 loops=1)
>
> Total runtime: .169 ms ;
>
> Like *how* *come*? There are indexes on both columns of the join. Is
> it the NUMERIC datatype messing things up? Unlikely, as I've seen the
> same with INTEGERs.
>
> If it is data dependent (these tables are presently empty), any
> suggestions as to how to tune a database for unknown mixes of data?
>
> This is run on the Windows version of PG, but I'm seeing the same kind
> of thing on Linux.
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>