From: Ioannis Anagnostopoulos <ioannis@anatec.com>
To: pgsql-performance@postgresql.org
Subject: Sequential scan instead of index scan
Date: Mon, 06 Aug 2012 16:08:05 +0100
Message-ID: <501FDDD5.2000106@anatec.com> (raw)
Hi, my query is very simple:
select
msg_id,
msg_type,
ship_pos_messages.pos_georef1,
ship_pos_messages.pos_georef2,
ship_pos_messages.pos_georef3,
ship_pos_messages.pos_georef4,
obj_id,
ship_speed,
ship_heading,
ship_course,
pos_point
from
feed_all_y2012m08.ship_pos_messages
where
extract('day' from msg_date_rec) = 1
AND msg_id = any(ARRAY[7294724,14174174,22254408]);
The msg_id is the pkey on the ship_pos_messages table and in this
example it is working fast as it uses the pkey (primary key index) to
make the selection. The expplain anayze follows:
"Result (cost=0.00..86.16 rows=5 width=117) (actual
time=128.734..163.319 rows=3 loops=1)"
" -> Append (cost=0.00..86.16 rows=5 width=117) (actual
time=128.732..163.315 rows=3 loops=1)"
" -> Seq Scan on ship_pos_messages (cost=0.00..0.00 rows=1
width=100) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((msg_id = ANY
('{7294724,14174174,22254408}'::integer[])) AND (date_part('day'::text,
msg_date_rec) = 1::double precision))"
" -> Seq Scan on ship_a_pos_messages ship_pos_messages
(cost=0.00..0.00 rows=1 width=100) (actual time=0.000..0.000 rows=0
loops=1)"
" Filter: ((msg_id = ANY
('{7294724,14174174,22254408}'::integer[])) AND (date_part('day'::text,
msg_date_rec) = 1::double precision))"
" -> Bitmap Heap Scan on ship_b_std_pos_messages
ship_pos_messages (cost=13.41..25.42 rows=1 width=128) (actual
time=49.127..49.127 rows=0 loops=1)"
" Recheck Cond: (msg_id = ANY
('{7294724,14174174,22254408}'::integer[]))"
" Filter: (date_part('day'::text, msg_date_rec) = 1::double
precision)"
" -> Bitmap Index Scan on ship_b_std_pos_messages_pkey
(cost=0.00..13.41 rows=3 width=0) (actual time=49.125..49.125 rows=0
loops=1)"
" Index Cond: (msg_id = ANY
('{7294724,14174174,22254408}'::integer[]))"
" -> Bitmap Heap Scan on ship_b_ext_pos_messages
ship_pos_messages (cost=12.80..24.62 rows=1 width=128) (actual
time=0.029..0.029 rows=0 loops=1)"
" Recheck Cond: (msg_id = ANY
('{7294724,14174174,22254408}'::integer[]))"
" Filter: (date_part('day'::text, msg_date_rec) = 1::double
precision)"
" -> Bitmap Index Scan on ship_b_ext_pos_messages_pkey
(cost=0.00..12.80 rows=3 width=0) (actual time=0.027..0.027 rows=0 loops=1)"
" Index Cond: (msg_id = ANY
('{7294724,14174174,22254408}'::integer[]))"
" -> Bitmap Heap Scan on ship_a_pos_messages_wk0
ship_pos_messages (cost=24.08..36.12 rows=1 width=128) (actual
time=79.572..114.152 rows=3 loops=1)"
" Recheck Cond: (msg_id = ANY
('{7294724,14174174,22254408}'::integer[]))"
" Filter: (date_part('day'::text, msg_date_rec) = 1::double
precision)"
" -> Bitmap Index Scan on ship_a_pos_messages_wk0_pkey
(cost=0.00..24.08 rows=3 width=0) (actual time=67.441..67.441 rows=3
loops=1)"
" Index Cond: (msg_id = ANY
('{7294724,14174174,22254408}'::integer[]))"
"Total runtime: 180.146 ms"
I think this is a pretty good plan and quite quick given the size of the
table (88Million rows at present). However in real life the parameter
where I search for msg_id is not an array of 3 ids but of 300.000 or
more. It is then that the query forgets the plan and goes to sequential
scan. Is there any way around? Or is this the best I can have?
Kind Regards
Yiannis
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: ioannis@anatec.com
Subject: Re: Sequential scan instead of index scan
In-Reply-To: <501FDDD5.2000106@anatec.com>
* 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