From: | Peter Bierman <bierman(at)apple(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: why doesn't an index help my simple query? |
Date: | 2003-05-31 03:05:23 |
Message-ID: | a05210209bafdcab5c052@[17.202.21.231] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-05-31 03:23:29 | Re: why doesn't an index help my simple query? |
Previous Message | Don Patou | 2003-05-31 02:49:18 | Re: using queries as default value? |