Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id AD113B5DBE4 for ; Thu, 9 Jun 2011 03:04:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 05054-09 for ; Thu, 9 Jun 2011 06:04:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail2.symstream.com (2.102.96.58.static.exetel.com.au [58.96.102.2]) by mail.postgresql.org (Postfix) with ESMTP id F0D79B5D801 for ; Thu, 9 Jun 2011 03:04:45 -0300 (ADT) Received: from symstream.com (unknown [192.168.0.254]) by mail2.symstream.com (Postfix) with SMTP id 1D7666ACED for ; Thu, 9 Jun 2011 16:04:43 +1000 (EST) Received: (qmail 21078 invoked by uid 453); 9 Jun 2011 06:04:42 -0000 X-Virus-Checked: Checked by ClamAV on symstream.com Received: from Unknown (HELO ajs-desktop.symstream.com) (192.168.0.22) by symstream.com (qpsmtpd/0.40) with ESMTP; Thu, 09 Jun 2011 16:04:42 +1000 From: anthony.shipman@symstream.com To: tv@fuzzy.cz Subject: Re: strange query plan with LIMIT Date: Thu, 9 Jun 2011 16:04:42 +1000 User-Agent: KMail/1.9.4 Cc: pgsql-performance@postgresql.org References: <201106071802.08272.anthony.shipman@symstream.com> <201106081834.07263.anthony.shipman@symstream.com> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <201106091604.42186.anthony.shipman@symstream.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201106/90 X-Sequence-Number: 44023 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 Do you mean create index tdiag_index2 ON tdiag(diag_id, create_time); Should this be in addition to or instead of the single index on create_time? I must be doing something really wrong to get this to happen: symstream2=> select count(*) from tdiag where create_time <= '2011-05-23 03:51:00.131597+0'; count ------- 0 (1 row) symstream2=> explain analyze select count(*) from tdiag where create_time <= '2011-05-23 03:51:00.131597+0'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=863867.21..863867.22 rows=1 width=0) (actual time=58994.078..58994.080 rows=1 loops=1) -> Seq Scan on tdiag (cost=0.00..844188.68 rows=7871413 width=0) (actual time=58994.063..58994.063 rows=0 loops=1) Filter: (create_time <= '2011-05-23 13:51:00.131597+10'::timestamp with time zone) Total runtime: 58994.172 ms (4 rows) symstream2=> \d tdiag Table "public.tdiag" Column | Type | Modifiers -------------+--------------------------+----------------------------------------------------------- diag_id | integer | not null default nextval(('diag_id_seq'::text)::regclass) create_time | timestamp with time zone | default now() diag_time | timestamp with time zone | not null device_id | integer | fleet_id | integer | customer_id | integer | module | character varying | node_kind | smallint | diag_level | smallint | message | character varying | not null default ''::character varying options | text | tag | character varying | not null default ''::character varying Indexes: "tdiag_pkey" PRIMARY KEY, btree (diag_id) "tdiag_create_time" btree (create_time) -- Anthony Shipman | Programming is like sex: One mistake and Anthony.Shipman@symstream.com | you're providing support for a lifetime.