Thread: indices - used by which user ?

indices - used by which user ?

From
Gerhard Hintermayer
Date:
I have two tables (2000-4000 entries each) owned by the postgres
superuser and built an index on one of them. If I connect to the
database as user postgres and do a natural inner join on the two tables
(the index coloumn) the index is used and my query executes at about 80
ms (verified with explain analyze). If I connect as another user (for
whom access to the two tables is also granted) and do the same query,
the index is not used and the query takes ~ 800 ms. Both cases are under
psql. Additionally, after   issuing a \c - <same other user> when
connected as the not postgres user, the index will be used (very strange).
I searched the docs, but found no kind of grant, that works on indices.
Are there any access priveledges on indices or am I missing some other
point ? Ah, nearly forgot - Version 7.3.1

Gerhard


Re: indices - used by which user ?

From
Stephan Szabo
Date:
On Wed, 7 May 2003, Gerhard Hintermayer wrote:

> I have two tables (2000-4000 entries each) owned by the postgres
> superuser and built an index on one of them. If I connect to the
> database as user postgres and do a natural inner join on the two tables
> (the index coloumn) the index is used and my query executes at about 80
> ms (verified with explain analyze). If I connect as another user (for
> whom access to the two tables is also granted) and do the same query,
> the index is not used and the query takes ~ 800 ms. Both cases are under
> psql. Additionally, after   issuing a \c - <same other user> when
> connected as the not postgres user, the index will be used (very strange).
> I searched the docs, but found no kind of grant, that works on indices.
> Are there any access priveledges on indices or am I missing some other
> point ? Ah, nearly forgot - Version 7.3.1

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?


Re: indices - used by which user ?

From
Date:
> 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


Re: indices - used by which user ?

From
Date:
After investigating further, I found out, that the index will also be not
used if I do a psql -U postgres <database>, unless I issue a \c command,
in fact even \c - does it.

The other thing I found out: the joined colums are of type character
varying(10) and text, in this case the index (of the table having the text
column) will be used (after the \c trick), I have similiar other
databases, where both joined columns have the same type (character
varying(10)) and the index is never ever used, which does'nt make sense to
me.
Don't ask me wy the types are different, might be due to a dump/reload
during several upgrades.
Fact is, that the runtime of the query in the other (those having equal
types) databases  is about as fast as in the one with different types when
using indices.
But why does joining columns with different types use indices, whereas
equal type joins do not use indices.


Gerhard


Re: indices - used by which user ?

From
Manfred Koizar
Date:
On Thu, 8 May 2003 09:20:31 +0200 (CEST), <g.hintermayer@inode.at>
wrote:
>So that's a factor of about 10 faster, only by changing the user, very
>strange.

Very, very strange!  Compare the outputs of SHOW ALL for both cases.
If there are any differences, please inform us.

>         ->  Seq Scan on produkt  (cost=0.00..2417.41 rows=2141 width=40)
                                               ^^^^         ^^^^
>                                  (actual time=0.02..27.12 rows=2141 loops=1)

Unless I'm missing something, your produkt table has more pages than
tuples.  VACUUM FULL should reduce its size to ca. 22 pages.

Servus
 Manfred


Re: indices - used by which user ?

From
Date:
> On Thu, 8 May 2003 09:20:31 +0200 (CEST), <g.hintermayer@inode.at>
> wrote:
>>So that's a factor of about 10 faster, only by changing the user, very
>> strange.
>
> Very, very strange!  Compare the outputs of SHOW ALL for both cases. If
> there are any differences, please inform us.
>
>>         ->  Seq Scan on produkt  (cost=0.00..2417.41 rows=2141
>> width=40)
>                                                ^^^^         ^^^^
>>                                  (actual time=0.02..27.12 rows=2141
>> loops=1)
>
> Unless I'm missing something, your produkt table has more pages than
> tuples.  VACUUM FULL should reduce its size to ca. 22 pages.
>
Could be, I'm running VACUUM only once a week.

Well the whole problem seems to be because of different types of the
joined columns.
I rebuilt my database to have the same datatype on the joined columns
(both character varying(10 now, before one text, one character
varying(10)) and my query works as fast as in the other databases.

Somebody shall correct me if I'm wrong, but that's what I found out:

The optimizer *never* uses an index when doing NATURAL INNER JOIN when the
joined rows have the same data type (at least I could'nt find a case where
he does) regardless if ther's an index on the joined column in one or both
tables or not.
If the joined rows have different datatypes (which should'nt be the normal
case) he sometimes does and sometimes doesn't. The slowdown og these joins
seems to be the type cast (character varying to text) and not the unused
index.

Gerhard


Re: indices - used by which user ?

From
Stephan Szabo
Date:
On Thu, 8 May 2003 g.hintermayer@inode.at wrote:

> > On Thu, 8 May 2003 09:20:31 +0200 (CEST), <g.hintermayer@inode.at>
> > wrote:
> >>So that's a factor of about 10 faster, only by changing the user, very
> >> strange.
> >
> > Very, very strange!  Compare the outputs of SHOW ALL for both cases. If
> > there are any differences, please inform us.
> >
> >>         ->  Seq Scan on produkt  (cost=0.00..2417.41 rows=2141
> >> width=40)
> >                                                ^^^^         ^^^^
> >>                                  (actual time=0.02..27.12 rows=2141
> >> loops=1)
> >
> > Unless I'm missing something, your produkt table has more pages than
> > tuples.  VACUUM FULL should reduce its size to ca. 22 pages.
> >
> Could be, I'm running VACUUM only once a week.
>
> Well the whole problem seems to be because of different types of the
> joined columns.
> I rebuilt my database to have the same datatype on the joined columns
> (both character varying(10 now, before one text, one character
> varying(10)) and my query works as fast as in the other databases.
>
> Somebody shall correct me if I'm wrong, but that's what I found out:
>
> The optimizer *never* uses an index when doing NATURAL INNER JOIN when the
> joined rows have the same data type (at least I could'nt find a case where
> he does) regardless if ther's an index on the joined column in one or both
> tables or not.

I can get it to in at least some circumstances:
create table t1(a int unique);
create table t2(a int unique);
explain select * from t1 natural inner join t2;

Also, I didn't see an index on produkt.p_code which may or may not help in
general.

I'd have said if possible to try with enable_seqscan=off, but if there's
no index on the other I'm not sure the numbers would be meaningful.


Re: indices - used by which user ?

From
Gerhard Hintermayer
Date:
Stephan Szabo wrote:
> On Thu, 8 May 2003 g.hintermayer@inode.at wrote:
>
>
>>>On Thu, 8 May 2003 09:20:31 +0200 (CEST), <g.hintermayer@inode.at>
>>>wrote:
>>>
>>>>So that's a factor of about 10 faster, only by changing the user, very
>>>>strange.
>>>
>>>Very, very strange!  Compare the outputs of SHOW ALL for both cases. If
>>>there are any differences, please inform us.
>>>
>>>
>>>>        ->  Seq Scan on produkt  (cost=0.00..2417.41 rows=2141
>>>>width=40)
>>>
>>>                                               ^^^^         ^^^^
>>>
>>>>                                 (actual time=0.02..27.12 rows=2141
>>>>loops=1)
>>>
>>>Unless I'm missing something, your produkt table has more pages than
>>>tuples.  VACUUM FULL should reduce its size to ca. 22 pages.
>>>
>>
>>Could be, I'm running VACUUM only once a week.
>>
>>Well the whole problem seems to be because of different types of the
>>joined columns.
>>I rebuilt my database to have the same datatype on the joined columns
>>(both character varying(10 now, before one text, one character
>>varying(10)) and my query works as fast as in the other databases.
>>
>>Somebody shall correct me if I'm wrong, but that's what I found out:
>>
>>The optimizer *never* uses an index when doing NATURAL INNER JOIN when the
>>joined rows have the same data type (at least I could'nt find a case where
>>he does) regardless if ther's an index on the joined column in one or both
>>tables or not.
>
>
> I can get it to in at least some circumstances:
> create table t1(a int unique);
> create table t2(a int unique);
> explain select * from t1 natural inner join t2;
>
> Also, I didn't see an index on produkt.p_code which may or may not help in
> general.
>
> I'd have said if possible to try with enable_seqscan=off, but if there's
> no index on the other I'm not sure the numbers would be meaningful.
>
>

You're right. When the joined columns have the same type, the index has
to be in produkt.p_code, then it is used. I have been misled, that in my
posted example the joined column had different types and the index on
auftrag_l?.p_code was used sometimes.
But interesting that the optimizer sometimes uses the index and
sometimes does'nt in that case. And that he uses the index of the other
table (auftrag_l?) and not of produkt.

Thanks for your help

Gerhard