subquery and table join, index not use for table
От | CoL |
---|---|
Тема | subquery and table join, index not use for table |
Дата | |
Msg-id | bu3gia$2lq0$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: subquery and table join, index not use for table
|
Список | 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.
В списке pgsql-performance по дате отправления: