subquery and table join, index not use for table

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.

Responses

Browse pgsql-performance by date

  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?