X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1C3AFD1D767 for ; Wed, 14 Jan 2004 13:36:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05190-10 for ; Wed, 14 Jan 2004 09:36:25 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 1803ED1B4C5 for ; Wed, 14 Jan 2004 09:36:22 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i0EDaGU6089732 for ; Wed, 14 Jan 2004 13:36:21 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i0EDZ7dp087906 for pgsql-performance@postgresql.org; Wed, 14 Jan 2004 13:35:07 GMT From: CoL X-Newsgroups: comp.databases.postgresql.performance Subject: subquery and table join, index not use for table Date: Wed, 14 Jan 2004 14:35:25 +0100 Organization: Hub.Org Networking Services Lines: 55 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/171 X-Sequence-Number: 5345 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.