Обсуждение: gin/gist indexes show twice

Поиск
Список
Период
Сортировка

gin/gist indexes show twice

От
Timon
Дата:
gin and gist indexes show twice in indexes list grid of table.
Watch attachment

--
All bugs reserved
Вложения

Re: gin/gist indexes show twice

От
Guillaume Lelarge
Дата:
Le 24/03/2011 07:21, Timon a écrit :
> gin and gist indexes show twice in indexes list grid of table.
> Watch attachment
>

Just tried, didn't reproduce it.

Care to share more info? like pgAdmin's release number. This issue
reminds me something, but I don't find what.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Re: gin/gist indexes show twice

От
Timon
Дата:
yup, seem that problem is not with git/gist indexes.
I have found the query for this grid in file
pgadmin/schema/pgIndex.cpp line 670.

I run this query in Sql Query tool and get same results:
SELECT
    indexrelname,
    idx_scan, idx_tup_read, idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)),
    refclassid, refobjid, deptype,
    refclassid::regclass, refobjid::regclass
FROM pg_stat_all_indexes stat
    JOIN pg_class cls ON cls.oid=indexrelid
    LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid
= cls.oid AND dep.refobjsubid = '0')
--    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid
AND con.oid = dep.refobjid)
WHERE schemaname = 'public'
    AND
    stat.relname = 'user_attr_text'
    --AND con.contype IS NULL
ORDER BY indexrelname

idx_fts;0;0;0;28 MB;3602;11335;n;pg_ts_config;11335
idx_fts;0;0;0;28 MB;1259;159262085;a;pg_class;user_attr_text
idx_length;0;0;0;4528 kB;1259;159262085;a;pg_class;user_attr_text
trgm_idx;0;0;0;126 MB;2616;24936297;n;pg_opclass;24936297
trgm_idx;0;0;0;126 MB;1259;159262085;a;pg_class;user_attr_text
user_attr_text_pkey;16;16;16;39 MB;2606;159262089;i;pg_constraint;159262089

my indexes:
CREATE INDEX idx_fts
  ON user_attr_text
  USING gin
  (strip(to_tsvector('russian'::regconfig, about::text)))
  WHERE about IS NOT NULL;

CREATE INDEX trgm_idx
  ON user_attr_text
  USING gin
  (lower(about::text) gin_trgm_ops)
  WHERE about IS NOT NULL;

this is on latest working version from git repo.

2011/3/28 Guillaume Lelarge <guillaume@lelarge.info>:
> Le 24/03/2011 07:21, Timon a écrit :
>> gin and gist indexes show twice in indexes list grid of table.
>> Watch attachment
>>
>
> Just tried, didn't reproduce it.
>
> Care to share more info? like pgAdmin's release number. This issue
> reminds me something, but I don't find what.
> --
> Guillaume
>  http://www.postgresql.fr
>  http://dalibo.com
>



--
All bugs reserved

Re: gin/gist indexes show twice

От
Guillaume Lelarge
Дата:
Le 28/03/2011 10:57, Timon a écrit :
> yup, seem that problem is not with git/gist indexes.
> I have found the query for this grid in file
> pgadmin/schema/pgIndex.cpp line 670.
>
> I run this query in Sql Query tool and get same results:
> SELECT
>     indexrelname,
>     idx_scan, idx_tup_read, idx_tup_fetch,
>     pg_size_pretty(pg_relation_size(indexrelid)),
>     refclassid, refobjid, deptype,
>     refclassid::regclass, refobjid::regclass
> FROM pg_stat_all_indexes stat
>     JOIN pg_class cls ON cls.oid=indexrelid
>     LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid
> = cls.oid AND dep.refobjsubid = '0')
> --    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid
> AND con.oid = dep.refobjid)
> WHERE schemaname = 'public'
>     AND
>     stat.relname = 'user_attr_text'
>     --AND con.contype IS NULL
> ORDER BY indexrelname
>

I'm wondering why you commented the left join on pg_constraint. It could
be your issue.

pgAdmin doesn't have these lines commented, and, AFAICT, it works. See

http://git.postgresql.org/gitweb?p=pgadmin3.git;a=blob;f=pgadmin/schema/pgIndex.cpp;h=4f270eedc03fd9e8fd3033ec627ca304462ff7bd;hb=HEAD#l670.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Re: gin/gist indexes show twice

От
Timon
Дата:
2011/3/28 Guillaume Lelarge <guillaume@lelarge.info>:
> Le 28/03/2011 10:57, Timon a écrit :
>> yup, seem that problem is not with git/gist indexes.
>> I have found the query for this grid in file
>> pgadmin/schema/pgIndex.cpp line 670.
>>
>> I run this query in Sql Query tool and get same results:
>> SELECT
>>       indexrelname,
>>       idx_scan, idx_tup_read, idx_tup_fetch,
>>       pg_size_pretty(pg_relation_size(indexrelid)),
>>       refclassid, refobjid, deptype,
>>       refclassid::regclass, refobjid::regclass
>> FROM pg_stat_all_indexes stat
>>       JOIN pg_class cls ON cls.oid=indexrelid
>>       LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid
>> = cls.oid AND dep.refobjsubid = '0')
>> --    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid
>> AND con.oid = dep.refobjid)
>> WHERE schemaname = 'public'
>>       AND
>>       stat.relname = 'user_attr_text'
>>       --AND con.contype IS NULL
>> ORDER BY indexrelname
>>
>
> I'm wondering why you commented the left join on pg_constraint. It could
> be your issue.
> pgAdmin doesn't have these lines commented, and, AFAICT, it works. See
>
http://git.postgresql.org/gitweb?p=pgadmin3.git;a=blob;f=pgadmin/schema/pgIndex.cpp;h=4f270eedc03fd9e8fd3033ec627ca304462ff7bd;hb=HEAD#l670.

I know it. I just want to pay attention on pg_depend. idx_fts has 2
dependencies - pg_ts_config;11335, pg_class;user_attr_text, and no
constraint dependencies. That's why it shows twitce.

my quick ugly patch:
diff --git a/pgadmin/schema/pgIndex.cpp b/pgadmin/schema/pgIndex.cpp
index 4f270ee..29abcfd 100644
--- a/pgadmin/schema/pgIndex.cpp
+++ b/pgadmin/schema/pgIndex.cpp
@@ -676,7 +676,7 @@ void pgIndexBaseCollection::ShowStatistics(frmMain
*form, ctlListView *statistic
        sql += wxT("\n")
               wxT("  FROM pg_stat_all_indexes stat\n")
               wxT("  JOIN pg_class cls ON cls.oid=indexrelid\n")
-              wxT("  LEFT JOIN pg_depend dep ON (dep.classid =
cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')\n")
+              wxT("  LEFT JOIN pg_depend dep ON (dep.classid =
cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND
refclassid::regclass::varchar = 'pg_constraint')\n")
               wxT("  LEFT OUTER JOIN pg_constraint con ON
(con.tableoid = dep.refclassid AND con.oid = dep.refobjid)\n")
               wxT("  WHERE schemaname = ") +
qtDbString(GetTable()->GetSchema()->GetName())
               + wxT(" AND stat.relname = ") + qtDbString(GetTable()->GetName())


>
>
> --
> Guillaume
>  http://www.postgresql.fr
>  http://dalibo.com
>



--
All bugs reserved

Re: gin/gist indexes show twice

От
Guillaume Lelarge
Дата:
Le 28/03/2011 12:10, Timon a écrit :
> 2011/3/28 Guillaume Lelarge <guillaume@lelarge.info>:
>> Le 28/03/2011 10:57, Timon a écrit :
>>> yup, seem that problem is not with git/gist indexes.
>>> I have found the query for this grid in file
>>> pgadmin/schema/pgIndex.cpp line 670.
>>>
>>> I run this query in Sql Query tool and get same results:
>>> SELECT
>>>       indexrelname,
>>>       idx_scan, idx_tup_read, idx_tup_fetch,
>>>       pg_size_pretty(pg_relation_size(indexrelid)),
>>>       refclassid, refobjid, deptype,
>>>       refclassid::regclass, refobjid::regclass
>>> FROM pg_stat_all_indexes stat
>>>       JOIN pg_class cls ON cls.oid=indexrelid
>>>       LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid
>>> = cls.oid AND dep.refobjsubid = '0')
>>> --    LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid
>>> AND con.oid = dep.refobjid)
>>> WHERE schemaname = 'public'
>>>       AND
>>>       stat.relname = 'user_attr_text'
>>>       --AND con.contype IS NULL
>>> ORDER BY indexrelname
>>>
>>
>> I'm wondering why you commented the left join on pg_constraint. It could
>> be your issue.
>> pgAdmin doesn't have these lines commented, and, AFAICT, it works. See
>>
http://git.postgresql.org/gitweb?p=pgadmin3.git;a=blob;f=pgadmin/schema/pgIndex.cpp;h=4f270eedc03fd9e8fd3033ec627ca304462ff7bd;hb=HEAD#l670.
>
> I know it. I just want to pay attention on pg_depend. idx_fts has 2
> dependencies - pg_ts_config;11335, pg_class;user_attr_text, and no
> constraint dependencies. That's why it shows twitce.
>
> my quick ugly patch:
> diff --git a/pgadmin/schema/pgIndex.cpp b/pgadmin/schema/pgIndex.cpp
> index 4f270ee..29abcfd 100644
> --- a/pgadmin/schema/pgIndex.cpp
> +++ b/pgadmin/schema/pgIndex.cpp
> @@ -676,7 +676,7 @@ void pgIndexBaseCollection::ShowStatistics(frmMain
> *form, ctlListView *statistic
>         sql += wxT("\n")
>                wxT("  FROM pg_stat_all_indexes stat\n")
>                wxT("  JOIN pg_class cls ON cls.oid=indexrelid\n")
> -              wxT("  LEFT JOIN pg_depend dep ON (dep.classid =
> cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0')\n")
> +              wxT("  LEFT JOIN pg_depend dep ON (dep.classid =
> cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND
> refclassid::regclass::varchar = 'pg_constraint')\n")
>                wxT("  LEFT OUTER JOIN pg_constraint con ON
> (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)\n")
>                wxT("  WHERE schemaname = ") +
> qtDbString(GetTable()->GetSchema()->GetName())
>                + wxT(" AND stat.relname = ") + qtDbString(GetTable()->GetName())
>

Can you share with us a small testcase? I mean some SQL DDL statements
that would helps us reproduce the issue? Thanks.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Re: gin/gist indexes show twice

От
Guillaume Lelarge
Дата:
Le 04/04/2011 09:44, Timon a écrit :
> CREATE TABLE _x
> (
>   v1 character varying
> );
>
> CREATE INDEX idx_gist
>   ON _x
>   USING gist
>   (to_tsvector('russian'::regconfig, v1::text));
>

OK, that helped me to reproduce it. It is now fixed, using your patch.
Unfortunately, it won't be part of 1.12.3.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com