At 10:29 PM -0400 5/30/03, Tom Lane wrote:
Peter Bierman <bierman@xxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
|
