From: anthony.shipman@symstream.com
To: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: strange query plan with LIMIT
Date: Thu, 9 Jun 2011 16:04:42 +1000
Message-ID: <201106091604.42186.anthony.shipman@symstream.com> (raw)
In-Reply-To: <c48d12d868ecc4b9ab6063f562650722.squirrel@sq.gransy.com>
References: <201106071802.08272.anthony.shipman@symstream.com>
<201106081834.07263.anthony.shipman@symstream.com>
<c48d12d868ecc4b9ab6063f562650722.squirrel@sq.gransy.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
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anthony.shipman@symstream.com, tv@fuzzy.cz
Subject: Re: strange query plan with LIMIT
In-Reply-To: <201106091604.42186.anthony.shipman@symstream.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox