[GENERAL] Can't get planner to use multicolumn index on large table

[Ulrich Meis]

Hi!

I am trying to get postgres to use my index but it just doesn't. Please
Help!
It follows the table definition and a series of commands I thought would
ensure usage of my index.

CREATE TABLE data.question_result (
id  bigserial   PRIMARY KEY,
trial_idbigint  NOT NULL REFERENCES data.trial(id),
question_id bigint  REFERENCES content.question(id),
,
);

mydb=# set enable_seqscan=false;
SET
mydb=# create unique index bothcols on data.question_result
(trial_id,question_id);
CREATE INDEX
mydb=# analyze verbose data.question_result;
INFO:  analyzing "data.question_result"
INFO:  "question_result": 4657 pages, 3000 rows sampled, 591439 estimated
total rows
ANALYZE
mydb=# explain analyze select * from data.question_result where trial_id=1
and question_id=2;
  QUERY
PLAN
--
 Seq Scan on question_result  (cost=1.00..100013528.58 rows=1
width=30) (actual time=883.641..883.641 rows=0 loops=1)
   Filter: ((trial_id = 1) AND (question_id = 2))
 Total runtime: 883.858 ms
(3 rows)

I inserted the data (not the schema) from a dump if that is of any
relevance.
I am running postgres 7.4.5 on gentoo linux 2.6.8.

Thanks for any Help,

Uli

P.S.: just did CLUSTER bothcols on data.question_result, analyze, explain
analyze...didn't help :-(

-- 
Geschenkt: 3 Monate GMX ProMail + 3 Top-Spielfilme auf DVD
++ Jetzt kostenlos testen http://www.gmx.net/de/go/mail ++

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] Howto handle views/Rules Privileges

[Ulrich Meis]

Hi!
Situation two schemas:
webviews: Contains a set of views with insert rules on them, accessed 
via a GUI.
devconf: "Business logic", all the tables, functions, triggers that I 
want to restrict access to as far as possible.Preferably, I wouldn't 
even grant usage to the schema.

I have a problem with the insert rules on the views :
The inserts triggered by the rules are executed with view owner 
privileges, but unfortunately any functions, triggers, sequence updates 
that are invoked by the rule NOT.

How do I solve this ?
My solution right now is to do "security definer" on all triggers and 
functions invoked by the rules, and to grant update privileges for the 
sequences. I hope there is a better way.

Thanks in advance for any comments and suggestions,
Uli



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])