Thread: Why doesn't it use indexes?

Why doesn't it use indexes?

From

Ahti Legonkov

Date:

03 July 2002, 04:58:21

Hi,

I have this query:
select * from reo inner join usr on reo.owner_id=usr.user_id

I also have created these indexes:
CREATE INDEX "owner_id_reo_key" ON reo (owner_id);
CREATE INDEX "user_id_user_key" ON usr (user_id);

Explain tells me this:
Merge Join  (cost=1341.74..1481.12 rows=299697 width=461)  ->  Sort  (cost=775.05..775.05 rows=6629 width=328)
-> Seq Scan on apartment_reo reo  (cost=0.00..354.29 rows=6629 
 
width=328)  ->  Sort  (cost=566.69..566.69 rows=4521 width=133)        ->  Seq Scan on USER usr  (cost=0.00..292.21
rows=4521width=133)
 

Why it does not use indexes I have created?

-- 
Ahti Legonkov

Re: Why doesn't it use indexes?

From

"Christopher Kings-Lynne"

Date:

03 July 2002, 07:43:51

1. ANALYZE both tables.  Go 'VACUUM ANALYZE;' to vacuum and analyze your
tables.  Analyzing means to update the planner statistics for the tables,
which might make Postgres use your indices.

2. If you tables are very small (eg. only a few hundred rows) then using an
index is usually slower than just scanning the table, so Postgres won't use
the index until the table grows.  (So long as you regularly update the
planner statistics)

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Ahti Legonkov
> Sent: Tuesday, 2 July 2002 11:47 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Why doesn't it use indexes?
>
>
> Hi,
>
> I have this query:
> select * from reo inner join usr on reo.owner_id=usr.user_id
>
> I also have created these indexes:
> CREATE INDEX "owner_id_reo_key" ON reo (owner_id);
> CREATE INDEX "user_id_user_key" ON usr (user_id);
>
> Explain tells me this:
> Merge Join  (cost=1341.74..1481.12 rows=299697 width=461)
>    ->  Sort  (cost=775.05..775.05 rows=6629 width=328)
>          ->  Seq Scan on apartment_reo reo  (cost=0.00..354.29 rows=6629
> width=328)
>    ->  Sort  (cost=566.69..566.69 rows=4521 width=133)
>          ->  Seq Scan on USER usr  (cost=0.00..292.21 rows=4521 width=133)
>
> Why it does not use indexes I have created?
>
> --
> Ahti Legonkov
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

Re: Why doesn't it use indexes?

From

Achilleus Mantzios

Date:

03 July 2002, 07:46:36

On Tue, 2 Jul 2002, Ahti Legonkov wrote:

Check the actual time by explain analyze.
If sequential scan (your table is small e.g.) is faster
then there is no need for index use.
Also check the enable_indexscan variable.

> Hi,
> 
> I have this query:
> select * from reo inner join usr on reo.owner_id=usr.user_id
> 
> I also have created these indexes:
> CREATE INDEX "owner_id_reo_key" ON reo (owner_id);
> CREATE INDEX "user_id_user_key" ON usr (user_id);
> 
> Explain tells me this:
> Merge Join  (cost=1341.74..1481.12 rows=299697 width=461)
>    ->  Sort  (cost=775.05..775.05 rows=6629 width=328)
>          ->  Seq Scan on apartment_reo reo  (cost=0.00..354.29 rows=6629 
> width=328)
>    ->  Sort  (cost=566.69..566.69 rows=4521 width=133)
>          ->  Seq Scan on USER usr  (cost=0.00..292.21 rows=4521 width=133)
> 
> Why it does not use indexes I have created?
> 
> 

-- 
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr