Re: Search query is curious

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: fiala_marek(at)centrum(dot)cz
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Search query is curious
Date: 2010-08-17 10:49:05
Message-ID: AANLkTinhq2oVXQVDMB6zzhQfsumrifcBS6_a5aSEw5uf@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello

2010/8/17 <fiala_marek(at)centrum(dot)cz>:
>
> Hi,
>
> I've database of lyrics and I'm using this query for suggest box.
> SELECT views, title, id FROM songs  WHERE title ILIKE 'bey%' ORDER BY views DESC LIMIT 15;
> In query plan is this line:  ->  Seq Scan on songs  (cost=0.00..11473.56 rows=5055 width=23) (actual time=1.088..89.863 rows=77 loops=1)
> it takes about 90ms
>
> but when i modify query (remove sort)
> SELECT views, title, id FROM songs  WHERE title ILIKE 'bey%' LIMIT 15;
> In query plan ->  Seq Scan on songs  (cost=0.00..11473.56 rows=5055 width=23) (actual time=1.020..20.601 rows=15 loops=1
> seq scan takes only 20ms now, why?
>
> Or any suggestion to optimize this query?

without ORDER BY database returns first 15 rows where predicate is
true. With ORDER BY the database has to find all rows where predicate
is true and then has to sort it. So first case can be a much faster
because there are not necessary full table scan.

regards

Pavel Stehule

> In table songs are about 150.000 rows.
>
> Thank you for your reply.
>
> Best regards.
> Marek Fiala
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Grittner 2010-08-17 14:18:21 Re: Very poor performance
Previous Message Thom Brown 2010-08-17 10:44:39 Re: Search query is curious