From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Matseas <smatseas@intrusic.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index usage
Date: Mon, 21 Aug 2006 14:26:19 -0400
Message-ID: <5599.1156184779@sss.pgh.pa.us> (raw)
In-Reply-To: <44E9EEF9.7050901@intrusic.com>
References: <44E9EEF9.7050901@intrusic.com>
Scott Matseas <smatseas@intrusic.com> writes:
> If I enable sequential scan the Index Cond in
> question gets replaced with a Seq scan.
What other planner parameters have you been fooling with?
With no data in the tables, I get a reasonably sane-looking plan,
so I'm thinking you've chosen bad values for something or other
(starting with enable_seqscan = off ;-))
explain
SELECT * FROM
last_summarized ls
JOIN tableA s ON s.table_idA > ls.summarized_id AND s.table_idA
<= ls.max_session_id
LEFT JOIN tableB sf ON s.table_idA = sf.table_idA AND sf.direction = 'a'::char
LEFT JOIN tableB sfb ON s.table_idA = sfb.table_idA AND sfb.direction = 'b'::char
WHERE ls.summary_name::text = 'summary'::text ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=6.16..54.51 rows=216 width=116)
-> Nested Loop Left Join (cost=6.16..42.05 rows=216 width=95)
-> Nested Loop (cost=6.16..29.58 rows=216 width=74)
-> Index Scan using last_summarized_pk on last_summarized ls (cost=0.00..8.02 rows=1 width=66)
Index Cond: ((summary_name)::text = 'summary'::text)
-> Bitmap Heap Scan on tablea s (cost=6.16..18.32 rows=216 width=8)
Recheck Cond: ((s.table_ida > ls.summarized_id) AND (s.table_ida <= ls.max_session_id))
-> Bitmap Index Scan on table_ida_pk (cost=0.00..6.16 rows=216 width=0)
Index Cond: ((s.table_ida > ls.summarized_id) AND (s.table_ida <= ls.max_session_id))
-> Index Scan using tableb_unq on tableb sfb (cost=0.00..0.05 rows=1 width=21)
Index Cond: ((s.table_ida = sfb.table_ida) AND (sfb.direction = 'b'::bpchar))
-> Index Scan using tableb_unq on tableb sf (cost=0.00..0.05 rows=1 width=21)
Index Cond: ((s.table_ida = sf.table_ida) AND (sf.direction = 'a'::bpchar))
(13 rows)
regards, tom lane
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: tgl@sss.pgh.pa.us, smatseas@intrusic.com
Subject: Re: Index usage
In-Reply-To: <5599.1156184779@sss.pgh.pa.us>
* 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