Re: JOIN between three *simple* tables ...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Marc G(dot) Fournier" <scrappy(at)hub(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: JOIN between three *simple* tables ...
Date: 2002-02-06 21:14:33
Message-ID: 8775.1013030073@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

"Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> iwantu=# \d poc_uid
> Index "poc_uid"
> Column | Type
> --------+--------
> uid | bigint
> btree

> iwantu=# explain select count(1) from orientation_c poc where uid = 1;
> NOTICE: QUERY PLAN:

> Aggregate (cost=2264.97..2264.97 rows=1 width=0)
> -> Seq Scan on orientation_c poc (cost=0.00..2264.96 rows=1 width=0)

> EXPLAIN

You're forgetting ye olde constant-casting problem. You need something
like

select count(1) from orientation_c poc where uid = 1::bigint;

to use an index on a bigint column.

Not sure about the other thing; have you VACUUM ANALYZEd (or at least
ANALYZEd) since filling the tables? It looks like the system thinks
the tables are much smaller than they really are.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ian Harding 2002-02-06 21:39:50 Re: [Fwd: MS SQL compatible functions]
Previous Message Greg Sabino Mullane 2002-02-06 21:07:29 Automatic transactions in psql

Browse pgsql-sql by date

  From Date Subject
Next Message Marc G. Fournier 2002-02-06 21:41:17 Re: JOIN between three *simple* tables ...
Previous Message Marc G. Fournier 2002-02-06 20:47:45 JOIN between three *simple* tables ...