Re: strange query plan with LIMIT

On Wednesday 08 June 2011 19:47, tv(at)fuzzy(dot)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(dot)Shipman(at)symstream(dot)com | you're providing support for a lifetime.