Hi all, Jumping in directly to the subject, this is what I get: explain SELECT bigint_col_1, bigint_col_2 FROM big_table WHERE bigint_col_2 in (12132131, null, null, null, null); QUERY PLAN ------------------------------------------------------------------- Seq Scan on big_table (cost=0.00..2447201.85 rows=448 width=16) Filter: ((bigint_col_2 = 12132131::bigint) OR NULL::boolean) (2 rows) Compared to: explain SELECT bigint_col_1, bigint_col_2 FROM big_table WHERE bigint_col_2 in (12132131, 123781, 1297839032, 123667123); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using dom_idx_member_bigint_col_2, dom_idx_member_bigint_col_2, dom_idx_member_bigint_col_2, dom_idx_member_bigint_col_2 on big_table (cost=0.00..6427.28 rows=1789 width=16) Index Cond: ((bigint_col_2 = 12132131) OR (bigint_col_2 = 123781) OR (bigint_col_2 = 1297839032) OR (bigint_col_2 = 123667123)) (2 rows) big_table has ~ 100 million rows. Considering that NULL::boolean is always false, I don't quite understand why the first query is going for a sequential scan, instead of just ignoring the nulls from the in clause... We have a lot of queries like this for chunking activities, and they work too on oracle. We've just migrated a bigger data base to postgres from oracle, and this is the first thing to slow down our system to a crawl... we will fix this, but the parser could be smarter I guess. Cheers, Csaba. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
