Re: Using LIMIT changes index used by planner

Andrew McMillan wrote:
> On Mon, 2004-12-13 at 01:13 -0500, Sven Willenberger wrote:
> 
>>I have a question regarding a serious performance hit taken when using a 
>>LIMIT clause. I am using version 7.4.6 on FreeBSD 4.10-STABLE with 2GB 
>>of memory. The table in question contains some 25 million rows with a 
>>bigserial primary key, orderdate index and a referrer index. The 2 
>>select statements are as follow:
> 
> 
> It's an interesting question, but to be able to get answers from this
> list you will need to provide "EXPLAIN ANALYZE ..." rather than just
> "EXPLAIN ...".
> 

A) Query without limit clause:
explain analyze select storelocation,order_number from custacct where 
referrer = 1365  and orderdate between '2004-12-07' and '2004-12-07 
12:00:00' order by custacctid;
 
        QUERY PLAN 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=1226485.32..1226538.78 rows=21382 width=43) (actual 
time=30340.322..30426.274 rows=21432 loops=1)
    Sort Key: custacctid
    ->  Index Scan using orderdate_idx on custacct 
(cost=0.00..1224947.52 rows=21382 width=43) (actual 
time=159.218..30196.686 rows=21432 loops=1)
          Index Cond: ((orderdate >= '2004-12-07 00:00:00'::timestamp 
without time zone) AND (orderdate <= '2004-12-07 12:00:00'::timestamp 
without time zone))
          Filter: (referrer = 1365)
  Total runtime: 30529.151 ms
(6 rows)

************************************

A2) Same query run again, to see effect of caching:
explain analyze select storelocation,order_number from custacct where 
referrer = 1365  and orderdate between '2004-12-07' and '2004-12-07 
12:00:00' order by custacctid;
 
        QUERY PLAN 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=1226485.32..1226538.78 rows=21382 width=43) (actual 
time=1402.410..1488.395 rows=21432 loops=1)
    Sort Key: custacctid
    ->  Index Scan using orderdate_idx on custacct 
(cost=0.00..1224947.52 rows=21382 width=43) (actual time=0.736..1259.964 
rows=21432 loops=1)
          Index Cond: ((orderdate >= '2004-12-07 00:00:00'::timestamp 
without time zone) AND (orderdate <= '2004-12-07 12:00:00'::timestamp 
without time zone))
          Filter: (referrer = 1365)
  Total runtime: 1590.675 ms
(6 rows)

***********************************

B) Query run with LIMIT

explain analyze select storelocation,order_number from custacct where 
referrer = 1365  and orderdate between '2004-12-07' and '2004-12-07 
12:00:00' order by custacctid limit 10;
 
                  QUERY PLAN 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..43065.76 rows=10 width=43) (actual 
time=1306957.216..1307072.111 rows=10 loops=1)
    ->  Index Scan using custacct2_pkey on custacct 
(cost=0.00..92083209.38 rows=21382 width=43) (actual 
time=1306957.205..1307072.017 rows=10 loops=1)
          Filter: ((referrer = 1365) AND (orderdate >= '2004-12-07 
00:00:00'::timestamp without time zone) AND (orderdate <= '2004-12-07 
12:00:00'::timestamp without time zone))
  Total runtime: 1307072.231 ms
(4 rows)

************************************

C) Query using the subselect variation

explain analyze select foo.storelocation, foo.order_number from (select 
storelocation,order_number from custacct where referrer = 1365  and 
orderdate between '2004-12-07' and '2004-12-07 12:00:00' order by 
custacctid) as foo  limit 10;
 
              QUERY PLAN 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=1226485.32..1226485.45 rows=10 width=100) (actual 
time=1413.829..1414.024 rows=10 loops=1)
    ->  Subquery Scan foo  (cost=1226485.32..1226752.60 rows=21382 
width=100) (actual time=1413.818..1413.933 rows=10 loops=1)
          ->  Sort  (cost=1226485.32..1226538.78 rows=21382 width=43) 
(actual time=1413.798..1413.834 rows=10 loops=1)
                Sort Key: custacctid
                ->  Index Scan using orderdate_idx on custacct 
(cost=0.00..1224947.52 rows=21382 width=43) (actual time=0.740..1272.380 
rows=21432 loops=1)
                      Index Cond: ((orderdate >= '2004-12-07 
00:00:00'::timestamp without time zone) AND (orderdate <= '2004-12-07 
12:00:00'::timestamp without time zone))
                      Filter: (referrer = 1365)
  Total runtime: 1418.964 ms
(8 rows)


Thanks,
Sven