pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy doesn't it use indexes?
3+ messages / 3 participants
[nested] [flat]
* Why doesn't it use indexes?
@ 2002-07-02 15:46 Ahti Legonkov <lego@127.0.0.1>
2002-07-03 07:42 ` Re: Why doesn't it use indexes? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-03 07:54 ` Re: Why doesn't it use indexes? Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ahti Legonkov @ 2002-07-02 15:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why doesn't it use indexes?
2002-07-02 15:46 Why doesn't it use indexes? Ahti Legonkov <lego@127.0.0.1>
@ 2002-07-03 07:42 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 3+ messages in thread
From: Christopher Kings-Lynne @ 2002-07-03 07:42 UTC (permalink / raw)
To: Ahti Legonkov <lego@127.0.0.1>; pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why doesn't it use indexes?
2002-07-02 15:46 Why doesn't it use indexes? Ahti Legonkov <lego@127.0.0.1>
@ 2002-07-03 07:54 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Achilleus Mantzios @ 2002-07-03 07:54 UTC (permalink / raw)
To: Ahti Legonkov <lego@127.0.0.1>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-07-03 07:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-02 15:46 Why doesn't it use indexes? Ahti Legonkov <lego@127.0.0.1>
2002-07-03 07:42 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-03 07:54 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox