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

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(dot)Grittner(at)wicourts(dot)gov>wrote:

> Mike Broers <mbroers(at)gmail(dot)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
>