Обсуждение: indices - used by which user ?
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
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?
> 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
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
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
> 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
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.
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