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.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html