Skip site navigation (1) Skip section navigation (2)

Re: long running query running too long

From: PC Drew <drewpc(at)ibsncentral(dot)com>
To: "Todd Fulton" <pongo(at)jah(dot)net>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: long running query running too long
Date: 2004-02-17 23:17:05
Message-ID: 668C432F-619F-11D8-9183-000393BDC2FE@ibsncentral.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Feb 17, 2004, at 1:41 PM, Todd Fulton wrote:
>
> spank_prod=# \d idx_spk_tgp_tgpid
>        Index "idx_spk_tgp_tgpid"
>     Column     |         Type
> ---------------+-----------------------
>  tgpid         | bigint
>  directoryname | character varying(64)
> btree
>

A couple of things to note:

1.  What version of PostgreSQL are you running?  I'm currently running 
7.3.4 and my output of \d on a table shows more index information than 
yours does.  If you're running anything earlier than 7.3, I'd 
definitely recommend that you upgrade.

2.  Why are you using a multicolumn index in this case?  You might want 
to read the page in the documentation that discusses multi-column 
indexes specifically.

http://www.postgresql.org/docs/7.4/interactive/indexes-multicolumn.html

In any case, it might even be the case that the index isn't being used 
at all.  Does anyone know if indexes are used in a case like this:

> spk_tgp t JOIN spk_tgplog l ON (t.tgpid = l.tgpid)

My hunch is that it's not used.  My understanding is that an index acts 
more as a shortcut so the database doesn't have to go through the 
entire table to look for specific values.  When joining two tables, 
however, you inherently have to go through the entire table.  If anyone 
can clarify this, that'd be great.

--
PC Drew


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-02-17 23:55:42
Subject: Re: long running query running too long
Previous:From: mattDate: 2004-02-17 22:34:52
Subject: Re: RAID or manual split?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group