From: | CoL <col(at)mportal(dot)hu> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | subquery and table join, index not use for table |
Date: | 2004-01-14 13:35:25 |
Message-ID: | bu3gia$2lq0$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi, I have to following select:
set enable_seqscan = on;
set enable_indexscan =on;
select a.levelno,a.id from (select 1 as levelno,42 as id) a, menutable b
where b.site_id='21' and a.id=b.id;
menutable:
id bigint,
site_id bigint
Indexes: menutable_pkey primary key btree (site_id, id),
The explain analyze shows:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..13.50 rows=1 width=34) (actual
time=0.04..0.43 rows=1 loops=1)
Join Filter: ("outer".id = "inner".id)
-> Subquery Scan a (cost=0.00..0.01 rows=1 width=0) (actual
time=0.01..0.01 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.00..0.00 rows=1 loops=1)
-> Seq Scan on menutable b (cost=0.00..13.01 rows=38 width=22)
(actual time=0.02..0.38 rows=38 loops=1)
Filter: (site_id = 21::bigint)
Total runtime: 0.47 msec
setting set enable_seqscan = off;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..29.85 rows=1 width=34) (actual
time=0.07..0.18 rows=1 loops=1)
Join Filter: ("outer".id = "inner".id)
-> Subquery Scan a (cost=0.00..0.01 rows=1 width=0) (actual
time=0.01..0.01 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.00..0.00 rows=1 loops=1)
-> Index Scan using menutable_pkey on menutable b
(cost=0.00..29.36 rows=38 width=22) (actual time=0.02..0.12 rows=38 loops=1)
Index Cond: (site_id = 21::bigint)
Total runtime: 0.22 msec
I do analyze, vacumm full analyze on table but nothing changed. The same
plan in case of join syntax.
version: PostgreSQL 7.3.3 and PostgreSQL 7.3.4
Any idea?
thx
C.
From | Date | Subject | |
---|---|---|---|
Next Message | Jón Ragnarsson | 2004-01-14 13:44:17 | Re: 100 simultaneous connections, critical limit? |
Previous Message | Christopher Browne | 2004-01-14 13:27:03 | Re: 100 simultaneous connections, critical limit? |