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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
