Re: ORDER BY ... LIMIT.. 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