Re: indices - used by which user ?

> I don't think there are separate access privs for indexes. Can you give
> the schema/query/explain analyze output for the query before and after
> the \c - <user> as a comparison point?

Jep:

Table "public.auftrag_l2"
Column | Type | Modifiers
-----------+------------------------+-----------
p_code | text |
variety | text |
a_nr | integer |
sub_nr | integer |
ch_nr | real |
ch_nr_sfm | real |
case_nr | integer |
datum | date |
zeit | time without time zone |
dauer | integer |
print | text |
format | character(1) |
gramm | real |
brand | text |
outrate | smallint |
reliab | smallint |
seq | integer |
tech | character varying(3) |
Indexes: idx_auftrag_l2 unique btree (a_nr),
idx_auftrag_l2_pcode btree (p_code),
idx_auftrag_l2_seq btree (seq),
idx_auftrag_l2_variety btree (variety)

Table "public.produkt"
Column | Type | Modifiers
--------+-----------------------+-----------
p_code | character varying(10) |
p_name | character varying(30) |

Query/Explain as non postgres user:

explain analyze SELECT * FROM auftrag_l2 NATURAL INNER JOIN produkt WHERE
datum>='01-05-2003' AND datum<'09-05-2003' ORDER BY sub_nr,datum,zeit;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Sort (cost=37417.56..37417.59 rows=15 width=139) (actual
time=776.29..776.31 rows=26 loops=1)
Sort Key: auftrag_l2.sub_nr, auftrag_l2.datum, auftrag_l2.zeit
-> Nested Loop (cost=0.00..37417.26 rows=15 width=139) (actual
time=25.66..776.00 rows=26 loops=1)
Join Filter: ("outer".p_code = ("inner".p_code)::text)
-> Seq Scan on auftrag_l2 (cost=0.00..134.47 rows=15 width=99)
(actual time=3.19..4.30 rows=26 loops=1)
Filter: ((datum >= '01-05-2003'::date) AND (datum <
'09-05-2003'::date))
-> Seq Scan on produkt (cost=0.00..2417.41 rows=2141 width=40)
(actual time=0.01..25.11 rows=2141 loops=26)
Total runtime: 776.49 msec

reconnect as same user (very strange) or as postgres user \c - aprol or \c
postgres
different query plan, no idea why:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=69891.40..69892.03 rows=250 width=139) (actual
time=82.42..82.65 rows=257 loops=1)
Sort Key: auftrag_l2.sub_nr, auftrag_l2.datum, auftrag_l2.zeit
-> Nested Loop (cost=0.00..69881.45 rows=250 width=139) (actual
time=25.93..80.74 rows=257 loops=1)
-> Seq Scan on produkt (cost=0.00..2417.41 rows=2141 width=40)
(actual time=0.02..27.12 rows=2141 loops=1)
-> Index Scan using idx_auftrag_l2_pcode on auftrag_l2
(cost=0.00..31.49 rows=2 width=99) (actual time=0.02..0.02 rows=0
loops=2141)
Index Cond: (auftrag_l2.p_code = ("outer".p_code)::text)
Filter: ((datum >= '2003-01-05'::date) AND (datum <
'2003-09-05'::date))
Total runtime: 83.11 msec

So that's a factor of about 10 faster, only by changing the user, very
strange.
Hope the output is readable despite the long lines.

Thanks vor any help
Gerhard