Re: plan question - query with order by and limit not choosing index depends on size of limit, table - Mailing list pgsql-performance

Try
order by created_at+0

On 1/6/11, Mike Broers <mbroers@gmail.com> wrote:
> Thanks for the assistance.
>
> Here is an explain analyze of the query with the problem limit:
>
> production=# explain analyze select * from landing_page.messages where
> ((messages.topic = E'x') AND (messages.processed = 'f'))  ORDER BY
> messages.created_at ASC limit 10;
>
>
>    QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------
> ------------------------------------------------------------
>  Limit  (cost=0.00..2891.06 rows=10 width=1340) (actual
> time=207922.586..207922.586 rows=0 loops=1)
>    ->  Index Scan using idx_landing_page_messages_created_at on messages
>  (cost=0.00..449560.48 rows=1555 widt
> h=1340) (actual time=207922.581..207922.581 rows=0 loops=1)
>          Filter: ((NOT processed) AND ((topic)::text = 'x'::text))
>  Total runtime: 207949.413 ms
> (4 rows)
>
>
> and an explain analyze with a higher limit that hits the index:
>
>
> production=# explain analyze select * from landing_page.messages where
> ((messages.topic = E'x') AND (messages.processed = 'f'))  ORDER BY
> messages.created_at ASC limit 25;
>                                                                       QUERY
> PLAN
>
> --------------------------------------------------------------------------------------------------------------
> -----------------------------------------
>  Limit  (cost=5885.47..5885.54 rows=25 width=1340) (actual
> time=80.931..80.931 rows=0 loops=1)
>    ->  Sort  (cost=5885.47..5889.36 rows=1555 width=1340) (actual
> time=80.926..80.926 rows=0 loops=1)
>          Sort Key: created_at
>          Sort Method:  quicksort  Memory: 17kB
>          ->  Bitmap Heap Scan on messages  (cost=60.45..5841.59 rows=1555
> width=1340) (actual time=64.404..64.
> 404 rows=0 loops=1)
>                Recheck Cond: ((topic)::text = 'x'::text)
>                Filter: (NOT processed)
>                ->  Bitmap Index Scan on idx_messages_topic_processed
>  (cost=0.00..60.06 rows=1550 width=0) (ac
> tual time=56.207..56.207 rows=0 loops=1)
>                      Index Cond: (((topic)::text = 'x'::text) AND (p
> rocessed = false))
>  Total runtime: 88.051 ms
> (10 rows)
>
>
> overrides in postgresql.conf
>
> shared_buffers = 256MB
> work_mem = 8MB
> max_fsm_pages = 2000000
> max_fsm_relations = 2000
> checkpoint_segments = 10
> archive_mode = on
> random_page_cost = 3.0
> effective_cache_size = 6GB
> default_statistics_target = 250
> logging_collector = on
>
>
> Forgot to mention this is Postgres 8.3.8 with 6GB memory on the server.
>
> When you ask how big is the active portion of the database I am not sure how
> to answer.  The whole database server is about 140GB, but there are other
> applications that use this database, this particular table is about 1.6GB
> and growing.  Currently there are jobs that query from this table every
> minute.
>
> Thanks again
> Mike
>
>
>
>
>
>
> On Wed, Jan 5, 2011 at 5:10 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov>wrote:
>
>> Mike Broers <mbroers@gmail.com> wrote:
>>
>> > Hello performance, I need help explaining the performance of a
>> > particular query
>>
>> You provided some of the information needed, but you should review
>> this page and post a bit more:
>>
>> http://wiki.postgresql.org/wiki/SlowQueryQuestions
>>
>> In particular, post the result of EXPLAIN ANALYZE, not just EXPLAIN.
>> Also, showing all overrides in your postgresql.conf file is
>> important, and some information about your hardware.  How big is the
>> active portion of your database (the frequently read portion)?
>>
>> > Why does the smaller limit cause it to skip the index?
>>
>> Because the optimizer thinks the query will return rows sooner that
>> way.
>>
>> > Is there a way to help the planner choose the better plan?
>>
>> You might get there by adjusting your memory settings and/or costing
>> settings, but we need to see more information to know that.
>>
>> -Kevin
>>
>

--
Sent from my mobile device

------------
pasman