From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Sequential scan instead of index scan |
Date: | 2012-08-07 16:00:55 |
Message-ID: | CAMkU=1wFbdZ0Rrkc2W-0eRkZNDYLrhHJRokoEUUvT8_8mMsHiQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Aug 6, 2012 at 8:08 AM, Ioannis Anagnostopoulos
<ioannis(at)anatec(dot)com> wrote:
> 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:
...
>
> 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?
What happens if you set "enable_seqscan=off" and run the query with
the very large list? (This is an experiment, not a recommendation for
production use)
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Ioannis Anagnostopoulos | 2012-08-07 16:06:54 | Re: Sequential scan instead of index scan |
Previous Message | Craig James | 2012-08-07 14:37:42 | Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m |