Re: Index usage

Scott Matseas <smatseas(at)intrusic(dot)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