From: anthony.shipman@symstream.com
To: tv@fuzzy.cz
Subject: Re: strange query plan with LIMIT
Date: Fri, 10 Jun 2011 18:38:39 +1000
User-Agent: KMail/1.9.4
Cc: pgsql-performance@postgresql.org,
	"Claudio Freire" <klaussfreire@gmail.com>,
	Pavel Stehule <pavel.stehule@gmail.com>
References: <201106071802.08272.anthony.shipman@symstream.com>
	<201106081834.07263.anthony.shipman@symstream.com>
	<c48d12d868ecc4b9ab6063f562650722.squirrel@sq.gransy.com>
In-Reply-To: <c48d12d868ecc4b9ab6063f562650722.squirrel@sq.gransy.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201106101838.39781.anthony.shipman@symstream.com>

On Wednesday 08 June 2011 19:47, tv@fuzzy.cz wrote:
> Have you tried to create a composite index on those two columns? Not sure
> if that helps but I'd try that.
>
> Tomas

This finally works well enough

CREATE TABLE tdiag (
    diag_id             integer DEFAULT nextval('diag_id_seq'::text),
    create_time		timestamp with time zone default now(),
....
   PRIMARY KEY (diag_id)
);

-- ************ COMPOSITE INDEX
create index tdiag_id_create on tdiag(diag_id, create_time);

alter table tdiag alter column diag_id set statistics 1000;
alter table tdiag alter column create_time set statistics 1000;

and then just do the original query

symstream2=> explain analyze select * from tdiag where
symstream2-> (create_time >= '2011-06-07 02:00:00.000000+0' and create_time 
< '2011-06-10 07:58:03.000000+0') and  diag_level <= 1
symstream2-> order by diag_id LIMIT 100 OFFSET 800;
                                                                            
QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6064.19..6822.21 rows=100 width=112) (actual 
time=1496.644..1497.094 rows=100 loops=1)
   ->  Index Scan using tdiag_id_create on tdiag  (cost=0.00..1320219.58 
rows=174166 width=112) (actual time=1409.285..1495.831 rows=900 loops=1)
         Index Cond: ((create_time >= '2011-06-07 12:00:00+10'::timestamp with 
time zone) AND (create_time < '2011-06-10 17:58:03+10'::timestamp with time 
zone))
         Filter: (diag_level <= 1)
 Total runtime: 1497.297 ms


If I had set the primary key to (diag_id, create_time) would simple queries on
diag_id still work well i.e.
    select * from tdiag where diag_id = 1234;

-- 
Anthony Shipman                 | -module(erlang).
Anthony.Shipman@symstream.com   | ''(_)->0. %-)