agora inbox for postgres@postgres.berkeley.edu  

help / color / mirror / Atom feed

From: Janne Soimasuo <jsoi@gis.joensuu.fi>
To: postgres@postgres.Berkeley.EDU
Subject: Problems with indeces ???
Date: Tue, 24 May 94 14:13:38 EET DST
Message-ID: <9405241113.AA06161@gis.joensuu.fi.noname> (raw)

Hi netters !

I think I have some kind of problem with postgres indeces or am I doing 
something wrong ?

I have a forest inventory database, which have among others following 
tables (classes): 
ObsUni(ID=int4, Name=char16, SDate=abstime,
                EDate=abstime, CDate=abstime, LayerId=char16, ObjectId=int4, 
                Typ_OU=int2, ObsUni=int4, RefPoi=int4 )
and
ObsVal(ObsUni=int4, Int_Val=int2,Value=float8)

ObsUni class have about 7000 tuples and ObsVal ~ 70 000.

ObsUni is joined with ObsVal, Typ_OU, ObsUni and RefPoi classes.

To make queries faster I made some indeces:
1st btree:
define index ObsUni_ind1b on ObsUni using btree (ID int4_ops)\g
define index ObsUni_ind5b on ObsUni using btree (Typ_OU int2_ops)\g
define index ObsUni_ind6b on ObsUni using btree (ObsUni int4_ops)\g

define index ObsVal_ind1b on ObsVal using btree (ObsUni int4_ops) \g
define index ObsVal_ind2b on ObsVal using btree (Int_Val int2_ops) \g

and then hash:
define index ObsUni_ind1h on ObsUni using hash (ID int4_ops)\g
define index ObsUni_ind5h on ObsUni using hash (Typ_OU int2_ops)\g
define index ObsUni_ind6h on ObsUni using hash (ObsUni int4_ops)\g

define index ObsVal_ind1h on ObsVal using hash (ObsUni int4_ops) \g
define index ObsVal_ind2h on ObsVal using hash (Int_Val int2_ops) \g

The system fails when I try to create hash indeces. Is there something wrong ?


Another problem is that when I make a query like:
retrieve (ObsUni.all) where ObsUni.ObsUni NOTNULL
I got answer:
FATAL: no response from backend: detected in PQexec

This query runs well if I don't have any indeces with class ObsUni but
after I've made those I got an error.

I've tried this with Linux and with SunOS, but no solution !
Postgres version is 4.2.beta

Thanks in advance !

Janne Soimasuo
Faculty of Forestry
University of Joensuu, Finland
jsoi@gis.joensuu.fi

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"

   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

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: postgres@postgres.berkeley.edu
  Cc: jsoi@gis.joensuu.fi
  Subject: Re: Problems with indeces ???
  In-Reply-To: <9405241113.AA06161@gis.joensuu.fi.noname>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox