Re: why doesn't an index help my simple query?

At 10:29 PM -0400 5/30/03, Tom Lane wrote:
>Peter Bierman <bierman(at)apple(dot)com> writes:
>> As you can see below, using an index doesn't seem to have any
>> significant impact on the query speed. Why not?
>
>> Index Scan Backward using events_time_key on events
>> (cost=0.00..4556.74 rows=71659 width=20) (actual time=0.84..16495.36
>> rows=16 loops=1)
>> Filter: (("time")::timestamp with time zone > (now() -
>>'00:02'::interval))
>
>Hm, why is that shown as a "filter" and not an "index condition"? And
>why is there an explicit conversion to timestamp with time zone in
>there? Better tell us about the exact data types involved here ...

I was hoping you'd say 'hm'. :-)

The pg_dump says (reordered with sample data at end)

CREATE TABLE therms (
sn character(16) NOT NULL,
tid integer NOT NULL,
name character varying(100) NOT NULL,
CONSTRAINT therms_sn CHECK ((length(btrim((sn)::text)) = 16))
);

CREATE TABLE events (
"time" timestamp without time zone DEFAULT
('now'::text)::timestamp(6) with time zone NOT NULL,
tid integer NOT NULL,
"temp" double precision NOT NULL
);

CREATE INDEX events_tid_idx ON events USING btree (tid);

ALTER TABLE ONLY therms
ADD CONSTRAINT therms_pkey PRIMARY KEY (sn);

ALTER TABLE ONLY therms
ADD CONSTRAINT therms_tid_key UNIQUE (tid);

ALTER TABLE ONLY events
ADD CONSTRAINT events_time_key UNIQUE ("time");

ALTER TABLE ONLY events
ADD CONSTRAINT "$1" FOREIGN KEY (tid) REFERENCES therms(tid) ON
UPDATE CASCADE ON DELETE NO ACTION;

COPY therms (sn, tid, name) FROM stdin;
1037c84800080005 1 Hot Tub
10e6a448000800c6 3 Shed 2
1010bb4800080015 4 Shed 3
105fe1480008006c 6 Outside
1083c24800080063 2 Roof
10d8d948000800b9 5 Shed 1
1047ec480008007a 7 Pool
10cdb448000800f4 8 Shed 4
\.

COPY events ("time", tid, "temp") FROM stdin;
2003-05-06 00:25:52.261602 2 55.17
2003-05-06 00:25:53.462081 4 55.27
2003-05-06 00:25:54.463235 5 55.06
2003-05-06 00:25:55.665572 1 63.16
2003-05-06 00:25:56.666579 3 55.17
2003-05-06 00:26:58.275967 2 55.17
\.

I created these tables using the following SQL:

CREATE TABLE therms
(
sn CHAR(16) PRIMARY KEY CHECK (length(trim(sn))=16),
tid INTEGER UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL
);

CREATE TABLE events
(
time TIMESTAMP UNIQUE NOT NULL DEFAULT CURRENT_TIMESTAMP,
tid INTEGER NOT NULL,
FOREIGN KEY(tid) REFERENCES therms(tid)
ON UPDATE CASCADE,
temp FLOAT NOT NULL
);

CREATE INDEX events_tid_idx ON events (tid);

-pmb