Try order by created_at+0 On 1/6/11, 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 >> > -- Sent from my mobile device ------------ pasman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
