Joins, Deletes and Indexes

From: Butkus_Charles(at)emc(dot)com
To: pgsql-performance(at)postgresql(dot)org
Subject: Joins, Deletes and Indexes
Date: 2005-02-23 01:45:44
Message-ID: 18C6A5982F816D47BFEF7DF47F7B03232E8BD2@corpmx2.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I've got 2 tables defined as follows:

CREATE TABLE "cluster"
(
id int8 NOT NULL DEFAULT nextval('serial'::text),
clusterid varchar(255) NOT NULL,
...
CONSTRAINT pk_cluster PRIMARY KEY (id)
)

CREATE TABLE sensorreport
(
id int8 NOT NULL DEFAULT nextval('serial'::text),
clusterid int8 NOT NULL,
...
CONSTRAINT pk_sensorreport PRIMARY KEY (id),
CONSTRAINT fk_sensorreport_clusterid FOREIGN KEY (clusterid) REFERENCES
"cluster" (id) ON UPDATE RESTRICT ON DELETE RESTRICT
)

I've defined an Index on the clusterid field of sensorreport.

So I've run into 2 issues, one a SELECT, the other a DELETE;

SELECT issue:
So the following query:
EXPLAIN ANALYZE select * from sensorreport where clusterid = 25000114;

Yields:
"Index Scan using idx_sensorreport_clusterid on sensorreport
(cost=0.00..2.01 rows=1 width=129) (actual time=0.000..0.000 rows=38
loops=1)"
" Index Cond: (clusterid = 25000114)"
"Total runtime: 0.000 ms"

However, when using a join as follows (in the cluster table id=25000114
clusterid='clusterid1'):
EXPLAIN ANALYZE select * from sensorreport as a join cluster as c on c.id =
a.clusterid where c.clusterid = 'clusterid1';

Yields:
Hash Join (cost=1.18..566211.51 rows=1071429 width=287) (actual
time=150025.000..150025.000 rows=38 loops=1)
Hash Cond: ("outer".clusterid = "inner".id)
-> Seq Scan on sensorreport a (cost=0.00..480496.03 rows=15000003
width=129) (actual time=10.000..126751.000 rows=15000039 loops=1)
-> Hash (cost=1.18..1.18 rows=1 width=158) (actual time=0.000..0.000
rows=0 loops=1)
-> Seq Scan on "cluster" c (cost=0.00..1.18 rows=1 width=158)
(actual time=0.000..0.000 rows=1 loops=1)
Filter: ((clusterid)::text = 'clusterid1'::text)
Total runtime: 150025.000 ms

My question is can I get the join query to use the
idx_sensorreport_clusterid index on the sensorreport table?

DELETE issue:
The statement:
EXPLAIN ANALYZE delete from cluster where clusterid='clusterid99'

Yields:
Seq Scan on "cluster" (cost=0.00..1.18 rows=1 width=6) (actual
time=0.000..0.000 rows=1 loops=1)
Filter: ((clusterid)::text = 'clusterid99'::text)
Total runtime: 275988.000 ms

I'm assuming that the length of the delete is because the "DELETE RESTRICT"
on the foreign key from sensortable.
Again, is there any way to get the delete to use the
idx_sensorreport_clusterid index?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Neil Conway 2005-02-23 04:58:51 Re: PostgreSQL is extremely slow on Windows
Previous Message Gaetano Mendola 2005-02-23 01:05:03 Re: is pg_autovacuum so effective ?