From: CoL <col@mportal.hu>
To: pgsql-performance@postgresql.org
Subject: subquery and table join, index not use for table
Date: Wed, 14 Jan 2004 14:35:25 +0100
Message-ID: <bu3gia$2lq0$1@news.hub.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: col@mportal.hu
Subject: Re: subquery and table join, index not use for table
In-Reply-To: <bu3gia$2lq0$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox