Re: long running query running too long

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