Thread: Re: Indexes generated for primary key are not show

 

> -----Original Message-----
> From: Constantin Stefanov [mailto:cstef@mail.ru] 
> Sent: 09 February 2004 10:25
> To: Dave Page
> Cc: Andreas Pflug; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Indexes generated for primary 
> key are not show
> 
> Well, I did not think you were talking about generated SQL. I 
> mean I would like to see these indexes in left part of main 
> pgadmin screen, where all other (user-created) indexes are 
> shown. I mostly do not look at the generated SQL, so I did 
> not think about it.
> Maybe for such indexes there should be no generated SQL when 
> you select it (or some text that warns that this index is 
> system-generated).

It's not so much about when you select those indexes, it's what happens
when you select the parent table and the index definition gets included
in the table definition (I realise that doesn't happen yet for indexes,
but the intention is that it will). That is of course, a relatively
minor problem.

The primary question remains though, why should we expose implementation
details of something like a primary key when we already show the primary
key itself? It's not like you cannot see the columns in it or anything.
Should we by the same argument show the triggers that implement foreign
keys, or rules that implement views?

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org

Dave Page wrote:

> The primary question remains though, why should we expose implementation
> details of something like a primary key when we already show the primary
> key itself? It's not like you cannot see the columns in it or anything.
> Should we by the same argument show the triggers that implement foreign
> keys, or rules that implement views?
I think at least to have an option (maybe not permanent, but something
like menu item 'show all details') would be useful. As for indexes - as
I said, they are needed for understanding planner desicions. As for
other parts (rules, auto-generated triggers etc.) - I don't know.
As for quick solution I would suggest checking with psql - if it can
shows something, then PgAdmin should have an option to show it. I think
many people use psql and it has almost all features needed for working
with PostgreSQL (but it is less convenient than PgAdmin). As for me,
when working with and something seems strange, it is psql where I look
for final answer.

-- 
Constantin Stefanov

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org

Dave Page wrote:

> The primary question remains though, why should we expose implementation
> details of something like a primary key when we already show the primary
> key itself? It's not like you cannot see the columns in it or anything.
> Should we by the same argument show the triggers that implement foreign
> keys, or rules that implement views?
I think at least to have an option (maybe not permanent, but something
like menu item 'show all details') would be useful. As for indexes - as
I said, they are needed for understanding planner desicions. As for
other parts (rules, auto-generated triggers etc.) - I don't know.
As for quick solution I would suggest checking with psql - if it can
shows something, then PgAdmin should have an option to show it. I think
many people use psql and it has almost all features needed for working
with PostgreSQL (but it is less convenient than PgAdmin). As for me,
when working with and something seems strange, it is psql where I look
for final answer.

-- 
Constantin Stefanov

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org

Constantin Stefanov wrote:

>As for quick solution I would suggest checking with psql - if it can
>shows something, then PgAdmin should have an option to show it. I think
>many people use psql and it has almost all features needed for working
>with PostgreSQL (but it is less convenient than PgAdmin). As for me,
>when working with and something seems strange, it is psql where I look
>for final answer.
>
>  
>
We deliberately do *not* support anything that psql does. psql bears a 
lot of ancient stuff, since it has developed from very early pgsql 
versions. psql works one level below pgAdmin3, showing all the raw stuff 
regardless whether it's useful nowadays or not, e.g. constraint 
triggers. pgAdmin shows the objects in a way that's suitable for pgsql 
7.3 and up, suppressing system stuff you might have become used to from 
<= 7.2 times. To work with pgAdmin3, your database should be 7.3 or up 
in every way, i.e. if updated from earlier version adddepend should have 
been executed.

Regards,
Andreas




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your     joining column's datatypes do not match

Constantin Stefanov wrote:

>As for quick solution I would suggest checking with psql - if it can
>shows something, then PgAdmin should have an option to show it. I think
>many people use psql and it has almost all features needed for working
>with PostgreSQL (but it is less convenient than PgAdmin). As for me,
>when working with and something seems strange, it is psql where I look
>for final answer.
>
>  
>
We deliberately do *not* support anything that psql does. psql bears a 
lot of ancient stuff, since it has developed from very early pgsql 
versions. psql works one level below pgAdmin3, showing all the raw stuff 
regardless whether it's useful nowadays or not, e.g. constraint 
triggers. pgAdmin shows the objects in a way that's suitable for pgsql 
7.3 and up, suppressing system stuff you might have become used to from 
<= 7.2 times. To work with pgAdmin3, your database should be 7.3 or up 
in every way, i.e. if updated from earlier version adddepend should have 
been executed.

Regards,
Andreas




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your     joining column's datatypes do not match

Andreas Pflug wrote:

> We deliberately do *not* support anything that psql does. psql bears a
> lot of ancient stuff, since it has developed from very early pgsql
> versions. psql works one level below pgAdmin3, showing all the raw stuff
> regardless whether it's useful nowadays or not, e.g. constraint
> triggers. pgAdmin shows the objects in a way that's suitable for pgsql
> 7.3 and up, suppressing system stuff you might have become used to from
> <= 7.2 times. To work with pgAdmin3, your database should be 7.3 or up
> in every way, i.e. if updated from earlier version adddepend should have
> been executed.
I do not suggest PgAdmin to become 'psql with GUI'. They are different
applications. But in some questionable issues I would suggest to use
psql as a reference, because it constains almost everything that is
needed for everyday work.
In case I explained PgAdmin does not provide enough information for
work, so I need to use another tool (psql). I am speaking only about
making PgAdmin fully sufficient for all the work which may be done
through database support.
Let me introduce 2 scenarios which explains what I mean.
Prologue is the same in both - creating table (I write SQL, but when I
stepped into the bug, I was doing the same with PgAdmin GUI).
CREATE TABLE test (a int,b int,PRIMARY KEY(a)
);
Now implicit index on column a is created, but it is not shown by PgAdmin.
I populate table with some data, use VACUUM ANALYZE to compute
statictics. Then I write query something like that
SELECT sum(b) WHERE a BETWEEN 1 AND 5;
Then 2 possible continuations.
1. I run EXPLAIN on that query, see index scan and begin to search for a
bug (I don't see index). Possible places for bug: planner (decides to
use index which is not present), PgAdmin (erraticaly parsing output),
PgAdmin (not showing index). The last is the true, but to realize it I
had to check with psql. Therefore - PgAdmin is not enough for
understanding what is happening.
2. I try to optimize query and create index with PgAdmin. Now I have 2
almost equivalent indexes wasting space and time. If planner decides to
use index created when creating primary key, we will come to scenario 1.
If it uses manually created index, I will not see that I have
unneseccary index which consumes disk space and processor time while
inserting new rows.

I got that it is quite a hard work to make PgAdmin show indexes from
primary key while not breaking other things. But I think it would be
nice if it falls into TODO list (as a distant entry or simply as a
suggestion).

Thanks.

-- 
Constatin Stefanov

Constantin Stefanov wrote:

>
>I do not suggest PgAdmin to become 'psql with GUI'. They are different
>applications. But in some questionable issues I would suggest to use
>psql as a reference, because it constains almost everything that is
>needed for everyday work.
>In case I explained PgAdmin does not provide enough information for
>work, so I need to use another tool (psql). I am speaking only about
>making PgAdmin fully sufficient for all the work which may be done
>through database support.
>Let me introduce 2 scenarios which explains what I mean.
>Prologue is the same in both - creating table (I write SQL, but when I
>stepped into the bug, I was doing the same with PgAdmin GUI).
>CREATE TABLE test (
>    a int,
>    b int,
>    PRIMARY KEY(a)
>);
>Now implicit index on column a is created, but it is not shown by PgAdmin.
>I populate table with some data, use VACUUM ANALYZE to compute
>statictics. Then I write query something like that
>SELECT sum(b) WHERE a BETWEEN 1 AND 5;
>Then 2 possible continuations.
>1. I run EXPLAIN on that query, see index scan and begin to search for a
>bug (I don't see index). Possible places for bug: planner (decides to
>use index which is not present), PgAdmin (erraticaly parsing output),
>PgAdmin (not showing index). The last is the true, but to realize it I
>had to check with psql. Therefore - PgAdmin is not enough for
>understanding what is happening.
>2. I try to optimize query and create index with PgAdmin. Now I have 2
>almost equivalent indexes wasting space and time. If planner decides to
>use index created when creating primary key, we will come to scenario 1.
>If it uses manually created index, I will not see that I have
>unneseccary index which consumes disk space and processor time while
>inserting new rows.
>
>I got that it is quite a hard work to make PgAdmin show indexes from
>primary key while not breaking other things. But I think it would be
>nice if it falls into TODO list (as a distant entry or simply as a
>suggestion).
>  
>
Well it's not really hard, just some things to think of. And the longer 
I think about it, and looking at your example, I'd say people analyzing 
a query should know a little what pgsql is doing, e.g. that unique and 
primary constraints include an index. Thus I tend to say we'll stay with 
what we got now.

Regards,
Andreas

Andreas Pflug wrote:

> Well it's not really hard, just some things to think of. And the longer
> I think about it, and looking at your example, I'd say people analyzing
> a query should know a little what pgsql is doing, e.g. that unique and
> primary constraints include an index. Thus I tend to say we'll stay with
> what we got now.
I can't completely agree with you. I use only PostgreSQL, but I used to
help my colleague working with Oracle (he was a novice), and I looked at
his result of EXPLAIN (it was different from what PostgreSQL gives, but
quite clear). I am not sure that in every database engine PRIMARY KEY
constraint is implemented using index. So you give some astonishment to
people not familiar with PostgreSQL but familiar with some other DBMS
and able to understand result of EXPLAIN.

-- 
Constantin Stefanov

> I can't completely agree with you. I use only PostgreSQL, but I used to
> help my colleague working with Oracle (he was a novice), and I looked at
> his result of EXPLAIN (it was different from what PostgreSQL gives, but
> quite clear). I am not sure that in every database engine PRIMARY KEY
> constraint is implemented using index. So you give some astonishment to
> people not familiar with PostgreSQL but familiar with some other DBMS
> and able to understand result of EXPLAIN.

In phpPgAdmin, we list them separately as well, however it did require 
some thinking.  In the end I decided to display things in the way the 
SQL spec lays them out.  All constraints together, etc.

Chris

Christopher Kings-Lynne wrote:
>> I can't completely agree with you. I use only PostgreSQL, but I used to
>> help my colleague working with Oracle (he was a novice), and I looked at
>> his result of EXPLAIN (it was different from what PostgreSQL gives, but
>> quite clear). I am not sure that in every database engine PRIMARY KEY
>> constraint is implemented using index. So you give some astonishment to
>> people not familiar with PostgreSQL but familiar with some other DBMS
>> and able to understand result of EXPLAIN.
> 
> In phpPgAdmin, we list them separately as well, however it did require
> some thinking.  In the end I decided to display things in the way the
> SQL spec lays them out.  All constraints together, etc.
And do you display indexes which are automatically generated by PRIMARY
KEY constraint? I would not say those indexes should be in generated SQL
- they certainly should not. But I say those indexes should be displayed
where user-created indexes are. Dave says they shouldn't - well, he is a
developer of PgAdmin, and I will use PgAdmin whether this feature will
be implemented or not, it is useful and convenient tool. But I asked for
it because in my opinion it will make PgAdmin more convenient. The final
desicion is, of course, made by developers, I only try to convince them
that my suggestion makes sense.

Thanks for your attention.

-- 
Constantin Stefanov

>>In phpPgAdmin, we list them separately as well, however it did require
>>some thinking.  In the end I decided to display things in the way the
>>SQL spec lays them out.  All constraints together, etc.
> 
> And do you display indexes which are automatically generated by PRIMARY
> KEY constraint? 

No.  The index name is same as constraint name anyway.  But we've never 
had anyone complain about it yet.

I would not say those indexes should be in generated SQL
> - they certainly should not. But I say those indexes should be displayed
> where user-created indexes are. Dave says they shouldn't - well, he is a
> developer of PgAdmin, and I will use PgAdmin whether this feature will
> be implemented or not, it is useful and convenient tool. But I asked for
> it because in my opinion it will make PgAdmin more convenient. The final
> desicion is, of course, made by developers, I only try to convince them
> that my suggestion makes sense.

It does, but it also adds weirdness, because the same thing is listed in 
two different places.  I'd consider adding them to the phpPgAdmin index 
view as sort of greyed out maybe, and under a heading 'constraint indexes'

Chris