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

Re: ORDER BY ... LIMIT.. performance

From: "john cartmell" <john(dot)cartmell(at)mediaburst(dot)co(dot)uk>
To: "Josh Berkus" <josh(at)agliodbs(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: ORDER BY ... LIMIT.. performance
Date: 2002-12-06 11:32:04
Message-ID: 94B61ED0D8770A4A98A3DBD72DBBA1F82132F4@mediaexch01.mediaburst.co.uk (view raw or flat)
Thread:
Lists: pgsql-performance


> 1) Post an EXPLAIN ANALYZE statement for the above query, with limit,
> that returns in 3-5 seconds.
> 2) Post an EXPLAIN ANALYZE for a query that returns slowly (20-30
> seconds).

The query:
SELECT * FROM tblcompany WHERE lower(companyname) like 'a g m%' ORDER BY
companyname;
returns 20 rows.
Its EXPLAIN ANALYZE is as follows:
	NOTICE:  QUERY PLAN:

	Sort  (cost=64196.18..64196.18 rows=6339 width=224) (actual
time=2274.64..2274.66 rows=20 loops=1)
	  ->  Seq Scan on tblcompany  (cost=0.00..63795.86 rows=6339
width=224) (actual time=1023.37..2274.41 rows=20 loops=1)
	Total runtime: 2274.78 msec

When limit is 19:
	EXPLAIN ANALYZE SELECT * FROM tblcompany WHERE
lower(companyname) like 'a g m%' ORDER BY companyname LIMIT 19,0;
	NOTICE:  QUERY PLAN:

	Limit  (cost=0.00..4621.68 rows=19 width=223) (actual
time=561.20..563.11 rows=19 loops=1)
	  ->  Index Scan using idx_tblcompany_companyname on tblcompany
(cost=0.00..1542006.83 rows=6339 width=223) (actual time=561.19..563.07
rows=20 loops=1)
	Total runtime: 563.22 msec


But when it is 20:
	EXPLAIN ANALYZE SELECT * FROM tblcompany WHERE
lower(companyname) like 'a g m%' ORDER BY companyname LIMIT 20,0;
	NOTICE:  QUERY PLAN:

	Limit  (cost=0.00..4864.92 rows=20 width=223) (actual
time=559.58..21895.02 rows=20 loops=1)
	  ->  Index Scan using idx_tblcompany_companyname on tblcompany
(cost=0.00..1542006.83 rows=6339 width=223) (actual
time=559.57..21894.97 rows=20 loops=1)
	Total runtime: 21895.13 msec


	Admitedly the query without the limit has a different query plan
but the last two don't and yet vary wildly.
	John Cartmell



Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2002-12-06 17:30:46
Subject: Speeding up aggregates
Previous:From: Bruce MomjianDate: 2002-12-06 05:08:02
Subject: Re: ALTER TABLE .. < ADD | DROP > OIDS

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