From: Tom Lane <tgl@sss.pgh.pa.us>
To: jugnooken <ken@jugnoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: WHERE with ORDER not using the best index
Date: Wed, 29 Jan 2014 18:15:55 -0500
Message-ID: <9852.1391037355@sss.pgh.pa.us> (raw)
In-Reply-To: <1391030357379-5789581.post@n5.nabble.com>
References: <1391030357379-5789581.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
jugnooken <ken@jugnoo.com> writes:
> Here's the query:
> db=> EXPLAIN ANALYSE SELECT social_feed_feed_items.social_message_id FROM
> social_feed_feed_items WHERE social_feed_feed_items.social_feed_id = 480
> ORDER BY posted_at DESC NULLS LAST LIMIT 1200;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=126.83..127.43 rows=1200 width=12) (actual time=10.321..13.694
> rows=1200 loops=1)
> -> Sort (cost=126.83..129.08 rows=4498 width=12) (actual
> time=10.318..11.485 rows=1200 loops=1)
> Sort Key: posted_at
> Sort Method: top-N heapsort Memory: 153kB
> -> Index Scan using index_social_feed_feed_items_on_social_feed_id
> on social_feed_feed_items (cost=0.09..76.33 rows=4498 width=12) (actual
> time=0.037..5.317 rows=4249 loops=1)
> Index Cond: (social_feed_id = 480)
> Total runtime: 14.913 ms
> (7 rows)
> I was hoping that they planner would use
> index_social_feed_feed_items_on_social_feed_id_and_posted_at, but it never
> does. If I manually remove the index that it currently uses then magic
> happens:
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.09..998.63 rows=1200 width=12) (actual time=0.027..3.792
> rows=1200 loops=1)
> -> Index Scan using
> index_social_feed_feed_items_on_social_feed_id_and_posted_at on
> social_feed_feed_items (cost=0.09..3742.95 rows=4498 width=12) (actual
> time=0.023..1.536 rows=1200 loops=1)
> Index Cond: (social_feed_id = 480)
> Total runtime: 4.966 ms
> (4 rows)
Well, it likes the first plan because it's estimating that one as cheaper
;-). The question is why the indexscan cost is estimated so remarkably
high for the second index --- nearly two orders of magnitude more to
retrieve the same number of index entries. The most obvious explanation
is that that index is horribly bloated for some reason. Have you checked
the physical index sizes? If the second index is many times bigger,
REINDEX ought to help, though it's unclear whether the bloat will recur.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ken@jugnoo.com
Subject: Re: WHERE with ORDER not using the best index
In-Reply-To: <9852.1391037355@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox