pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSearch query is curious
4+ messages / 4 participants
[nested] [flat]
* Search query is curious
@ 2010-08-17 07:26 fiala_marek@centrum.cz
0 siblings, 2 replies; 4+ messages in thread
From: fiala_marek@centrum.cz @ 2010-08-17 07:26 UTC (permalink / raw)
To: pgsql-performance
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?
In table songs are about 150.000 rows.
Thank you for your reply.
Best regards.
Marek Fiala
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Search query is curious
@ 2010-08-17 10:44 Thom Brown <thom@linux.com>
parent: fiala_marek@centrum.cz
1 sibling, 0 replies; 4+ messages in thread
From: Thom Brown @ 2010-08-17 10:44 UTC (permalink / raw)
To: fiala_marek@centrum.cz; +Cc: pgsql-performance
On 17 August 2010 08:26, <fiala_marek@centrum.cz> wrote:
>
> 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?
Sorts have a cost, so will take longer.
> Or any suggestion to optimize this query?
> In table songs are about 150.000 rows.
It might be an idea to add an index to your views column to prevent
the need for a sequential scan to sort. Also, ILIKE won't be able to
use an index, so if you wish to match against title, you may wish to
change your query to use:
WHERE lower(title) LIKE ....
And then create an index on lower(title).
Regards
--
Thom Brown
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Search query is curious
@ 2010-08-17 10:49 Pavel Stehule <pavel.stehule@gmail.com>
parent: fiala_marek@centrum.cz
1 sibling, 1 reply; 4+ messages in thread
From: Pavel Stehule @ 2010-08-17 10:49 UTC (permalink / raw)
To: fiala_marek@centrum.cz; +Cc: pgsql-performance
Hello
2010/8/17 <fiala_marek@centrum.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Search query is curious
@ 2010-08-17 15:16 Maciek Sakrejda <msakrejda@truviso.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Maciek Sakrejda @ 2010-08-17 15:16 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: fiala_marek@centrum.cz; pgsql-performance
> 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.
Right. Essentialy, the ORDER BY happens before the LIMIT (so you have
to sort everything before you take the first 15). If it were the other
way around, you would take the first 15 rows Postgres happens to find
(in an arbitrary order) and then sort these 15, which is probably not
that useful. Consider Thom's suggestion.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-08-17 15:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-17 07:26 Search query is curious fiala_marek@centrum.cz
2010-08-17 10:44 ` Thom Brown <thom@linux.com>
2010-08-17 10:49 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-08-17 15:16 ` Maciek Sakrejda <msakrejda@truviso.com>
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