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

[Mike Broers <mbroers@xxxxxxxxx>]

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.p@xxxxxxxxx>
To:Âpgsql-performance@xxxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxxxxx> wrote:

Mike Broers <mbroers@xxxxxxxxx> 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