From: Thomas Braad Toft <pgsql-general@magicx.dk>
To: pgsql-sql@postgresql.org
Subject: PostgreSQL ignores my indexes
Date: Wed, 23 Feb 2005 15:01:52 +0100 (CET)
Message-ID: <53775.193.162.192.11.1109167312.squirrel@webmail.tuffmail.net> (raw)
Hello,
I'm having two tables (listed):
CREATE TABLE "public"."device" (
"id" BIGSERIAL,
"name" TEXT,
"serialnumber" TEXT,
-- many more columns --
) WITH OIDS;
CREATE UNIQUE INDEX "device_id_key" ON "public"."device"
USING btree ("id");
CREATE INDEX "device_name_index" ON "public"."device"
USING hash ("name");
CREATE TABLE "public"."tmeevent" (
"id" BIGSERIAL,
"tme_endpointlabel" TEXT,
"tme_ip_address" INET,
"tme_gateway" TEXT,
"tme_tmeserver_id" TEXT,
"tme_action" TEXT,
"tme_argument" TEXT,
"tstamp" TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT now(),
"debug" BOOLEAN DEFAULT false,
"tme_status" TEXT,
CONSTRAINT "tmeevent_pkey" PRIMARY KEY("id")
) WITH OIDS;
CREATE INDEX "tmeevent_tmeendpointlabel_index" ON "public"."tmeevent"
USING hash ("tme_endpointlabel");
Table device contains 5285 rows, tmeevent contains 834912 rows.
I have to make a query like the one below:
SELECT device.id AS device_id, tme_endpointlabel, tme_ip_address,
tme_gateway,
tme_tmeserver_id, tme_action, tme_argument, tstamp, tme_status
FROM (tmeevent LEFT JOIN device ON ((tmeevent.tme_endpointlabel =
device.name)))
Doing an explain analyze on this query gives me:
Hash Join (cost=578.06..91208.59 rows=853428 width=167) (actual
time=35.22..8992.61 rows=835013 loops=1)
Hash Cond: ("outer".tme_endpointlabel = "inner".name)
-> Seq Scan on tmeevent (cost=0.00..23606.12 rows=834912 width=138)
(actual time=0.04..2193.97 rows=834912 loops=1)
-> Hash (cost=564.85..564.85 rows=5285 width=29) (actual
time=35.06..35.06 rows=0 loops=1)
-> Seq Scan on device (cost=0.00..564.85 rows=5285 width=29) (actual
time=0.04..25.07 rows=5285 loops=1)
Total runtime: 9499.58 msec
Why isn't the planner using my indexes? I tried making them as both rtree
and btree, but that doesn't seem to work.
I've been running VACUUM and ANALYZE on both tables, but it doesn't help.
Thanks in advance!
--
Thomas Braad Toft
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-sql@postgresql.org
Cc: pgsql-general@magicx.dk
Subject: Re: PostgreSQL ignores my indexes
In-Reply-To: <53775.193.162.192.11.1109167312.squirrel@webmail.tuffmail.net>
* 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