Sequential scan instead of index scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux