Search Postgresql Archives

Re: why is the LIMIT clause slowing down this SELECT?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 1 Aug 2007, Scott Marlowe wrote:

> On 8/1/07, Mason Hale <masonhale@xxxxxxxxx> wrote:
> > On a 8.1.9 version database that has been recently vacuumed and
> > analyzed, I'm seeing some dramatic performance degradation if a limit
> > clause is included in the query. This seems counter-intuitive to me.
> >
> > Here's the query and explain plan WITH the LIMIT clause:
> >
> > SELECT *
> >    FROM topic_feed
> >  WHERE topic_id = 106947234
> >   ORDER BY score DESC
> >      LIMIT 25
> >
> > Limit  (cost=0.00..651.69 rows=25 width=29) (actual
> > time=72644.652..72655.029 rows=25 loops=1)
> >   ->  Index Scan Backward using topic_feed_score_index on topic_feed
> > (cost=0.00..21219.08 rows=814 width=29) (actual
> > time=72644.644..72654.855 rows=25 loops=1)
> >         Filter: (topic_id = 106947234)
> > Total runtime: 72655.733 ms
> >
> > ==============
> >
> > and now WITHOUT the LIMIT clause:
> >
> > SELECT *
> >    FROM topic_feed
> >  WHERE topic_id = 106947234
> >   ORDER BY score DESC
> >
> > Sort  (cost=1683.75..1685.78 rows=814 width=29) (actual
> > time=900.553..902.267 rows=492 loops=1)
> >   Sort Key: score
> >   ->  Bitmap Heap Scan on topic_feed  (cost=7.85..1644.40 rows=814
> > width=29) (actual time=307.900..897.993 rows=492 loops=1)
> >         Recheck Cond: (topic_id = 106947234)
> >         ->  Bitmap Index Scan on
> > index_topic_feed_on_topic_id_and_feed_id  (cost=0.00..7.85 rows=814
> > width=0) (actual time=213.205..213.205 rows=2460 loops=1)
> >               Index Cond: (topic_id = 106947234)
> > Total runtime: 904.049 ms
>
> Something seems wrong here.  The cost of the second plan adds up to
> 1685, the cost of the first plan adds up to 651.69 with an
> intermediate step that adds up to 21219.08.  ??? I thought the outer
> parts of the plan always contained the inner parts?  This doesn't make
> sense.

I think it's because the top node is a limit node over a node that doesn't
need to run to completion in order to complete the request so it's
expecting an output cost about 25/814ths (limit 25 over 814 estimated
rows) of the input cost as it expects to only run that fraction of the
inner plan.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux