Обсуждение: PostgreSQL ignores my indexes
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
This is probably better on -performance, and is certainly a FAQ. But. . . On Wed, Feb 23, 2005 at 03:01:52PM +0100, Thomas Braad Toft wrote: > > Table device contains 5285 rows, tmeevent contains 834912 rows. ^^^^ ^^^^^^ > -> Seq Scan on tmeevent (cost=0.00..23606.12 rows=834912 width=138) > (actual time=0.04..2193.97 rows=834912 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) ^^^^ > Why isn't the planner using my indexes? I tried making them as both rtree Because there's no advantage to using an index when you are fetching 100% of both tables. This is the most efficient plan. Of course, it's an open question whether you want to get 100% of both tables. But that's what you're doing, and using the index would be more expoensive than this. A -- Andrew Sullivan | ajs@crankycanuck.ca Information security isn't a technological problem. It's an economics problem. --Bruce Schneier
Andrew Sullivan wrote: > This is probably better on -performance, and is certainly a FAQ. > But. . . > > On Wed, Feb 23, 2005 at 03:01:52PM +0100, Thomas Braad Toft wrote: > > > > Table device contains 5285 rows, tmeevent contains 834912 rows. > ^^^^ ^^^^^^ > > > -> Seq Scan on tmeevent (cost=0.00..23606.12 rows=834912 width=138) > > (actual time=0.04..2193.97 rows=834912 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) > ^^^^ > > > Why isn't the planner using my indexes? I tried making them as both rtree > > Because there's no advantage to using an index when you are fetching > 100% of both tables. This is the most efficient plan. Of course, > it's an open question whether you want to get 100% of both tables. > But that's what you're doing, and using the index would be more > expoensive than this. Right. The FAQ addresses this issue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073