pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL ignores my indexes
3+ messages / 3 participants
[nested] [flat]
* PostgreSQL ignores my indexes
@ 2005-02-23 14:01 Thomas Braad Toft <pgsql-general@magicx.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Braad Toft @ 2005-02-23 14:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL ignores my indexes
@ 2005-02-23 14:25 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Thomas Braad Toft <pgsql-general@magicx.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Andrew Sullivan @ 2005-02-23 14:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL ignores my indexes
@ 2005-02-25 00:49 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2005-02-25 00:49 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-02-25 00:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-23 14:01 PostgreSQL ignores my indexes Thomas Braad Toft <pgsql-general@magicx.dk>
2005-02-23 14:25 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-02-25 00:49 ` Bruce Momjian <pgman@candle.pha.pa.us>
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