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

Thanks for the suggestion,

created_at is a timestamp without time zone type column. When I add +0 to
created at I get a cast error. I am able to get the query to use the
desired index when increasing or removing the limit, and I am still looking
for the reason why that is happening. Any advice or more information I can
supply please let me know.

ERROR: operator does not exist: timestamp without time zone + integer
LINE 1: ...es.processed = 'f')) ORDER BY messages.created_at+0 ASC lim...
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.

From: "pasman pasmański" <pasman(dot)p(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Date: Fri, 7 Jan 2011 15:00:22 +0100
Subject: Re: plan question - query with order by and limit not choosing
index depends on size of limit, table
Try
order by created_at+0

On Thu, Jan 6, 2011 at 3:36 PM, Mike Broers <mbroers(at)gmail(dot)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(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
>>
>
>