Skip site navigation (1) Skip section navigation (2)

Re: Search query is curious

From: Maciek Sakrejda <msakrejda(at)truviso(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: fiala_marek(at)centrum(dot)cz, pgsql-performance(at)postgresql(dot)org
Subject: Re: Search query is curious
Date: 2010-08-17 15:16:25
Message-ID: AANLkTim0gvJ9xtvNEbRLxVM1DYBjOOYCZ5TzH5zb+Kzp@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: gnuoytrDate: 2010-08-17 15:27:56
Subject: Re: Quesion on the use of indexes
Previous:From: Alvaro HerreraDate: 2010-08-17 15:07:39
Subject: Re: Quesion on the use of indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group