Re: indices - used by which user ? - Mailing list pgsql-general

> 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