pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Butkus_Charles@emc.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Joins, Deletes and Indexes
Date: Wed, 23 Feb 2005 13:42:18 +0000
Message-ID: <421C883A.20708@archonet.com> (raw)
In-Reply-To: <18C6A5982F816D47BFEF7DF47F7B03232E8BD4@corpmx2.corp.emc.com>
References: <18C6A5982F816D47BFEF7DF47F7B03232E8BD4@corpmx2.corp.emc.com>

Butkus_Charles@emc.com wrote:
> 
> The cluster table only has 11 rows, so I'm not sure an index would
> help.  The sensorreport table has 15,000,000 rows so that's why I've
> got the index there.

Ah - only 11?

>>>on the foreign key from sensortable.
>>>Again, is there any way to get the delete to use the
>>>idx_sensorreport_clusterid index?
>>
>>No, because this is the cluster table, not sensorreport :-)
> 
> True, but the foreign key constraint on the sensorreport table forces
> Postgres to check if there are any sensorreport's that are currently
> using this cluster before allowing the cluster to be deleted.

If you only have 11 distinct values in the large table then it's 
debatable whether it's always quicker to use the index. Since your first 
example (clusterid = 25000114) returned so few rows, I'm guessing that 
some other values represent a sizeable percentage of the table. That'd 
explain the difference between PG's estimates and the actual number of 
matching rows.

You can try "SET enable_seqscan =false;" before running the query and 
see whether using the index helps things.

--
   Richard Huxton
   Archonet Ltd



view thread (4+ messages)

Message-ID: <421C883A.20708@archonet.com>
Permalink:  ../421C883A.20708@archonet.com/
Also on:    postgresql.org/message-id/421C883A.20708@archonet.com

 · 

reply

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-performance@postgresql.org
  Cc: dev@archonet.com, Butkus_Charles@emc.com
  Subject: Re: Joins, Deletes and Indexes
  In-Reply-To: <421C883A.20708@archonet.com>

* 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