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  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список 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 по дате отправления:

Предыдущее
От: Jón Ragnarsson
Дата:
Сообщение: 100 simultaneous connections, critical limit?
Следующее
От: "Jeremy M. Guthrie"
Дата:
Сообщение: Question about space usage: