Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 3C0FE182A958 for ; Mon, 6 Aug 2012 12:08:25 -0300 (ADT) Received: from mail.anatec.com ([87.84.168.242]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SyOur-0003bC-L1 for pgsql-performance@postgresql.org; Mon, 06 Aug 2012 15:08:24 +0000 Received: from [192.168.10.57] ([192.168.10.57]) by mail.anatec.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.4675); Mon, 6 Aug 2012 16:08:05 +0100 Message-ID: <501FDDD5.2000106@anatec.com> Date: Mon, 06 Aug 2012 16:08:05 +0100 From: Ioannis Anagnostopoulos User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:14.0) Gecko/20120713 Thunderbird/14.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Sequential scan instead of index scan Content-Type: multipart/alternative; boundary="------------030000080200070108080404" X-OriginalArrivalTime: 06 Aug 2012 15:08:05.0168 (UTC) FILETIME=[47B9DB00:01CD73E5] X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201208/24 X-Sequence-Number: 47562 This is a multi-part message in MIME format. --------------030000080200070108080404 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------030000080200070108080404 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------030000080200070108080404--