Re: Question about explain-command...

I will try answering your questions. Please note that I am a newbie myself.

Clemens Eisserer wrote

> All primary keys are indixed, and this is what explain tells me:
> Unique (cost=15.67..16.69 rows=34 width=115)
> -> Sort (cost=15.67..15.75 rows=34 width=115)
> Sort Key: customer.email, customer."key", customer.anrede, customer.str
> asse, customer.plz, customer.ort, customer.vorname, customer.nachname, custtype.
> name, customer.land, customer.datanotvalid
> -> Hash Left Join (cost=6.16..14.80 rows=34 width=115)
> Hash Cond: ("outer".prodgroup = "inner"."key")
> -> Hash Left Join (cost=4.97..13.10 rows=34 width=119)
> Hash Cond: ("outer".custgroup = "inner"."key")
> -> Hash Left Join (cost=3.88..11.49 rows=34 width=111)
> Hash Cond: ("outer".goodsid = "inner"."key")
> -> Hash Left Join (cost=1.98..9.08
> rows=34 width=11 1)
> Hash Cond: ("outer"."key" = "inner".custid)
> -> Seq Scan on customer (cost=0.00..6.10 rows =34 width=107)
> Filter: ((nachname)::text ~~
> '%au%'::text )
> -> Hash (cost=1.78..1.78 rows=78 width=8)
> -> Seq Scan on sells (cost=0.00..1.78 r ows=78 width=8)
> -> Hash (cost=1.72..1.72 rows=72 width=8)
> -> Seq Scan on goods (cost=0.00..1.72 rows=72 width=8)
> -> Hash (cost=1.08..1.08 rows=8 width=16)
> -> Seq Scan on custtype (cost=0.00..1.08
> rows=8 wid th=16)
> -> Hash (cost=1.15..1.15 rows=15 width=4)
> -> Seq Scan on prodtype (cost=0.00..1.15 rows=15 width=4)

> What does the hash-lines mean, does that mean my query does not use
> the indices at all?

Yes. Probably each table fits nicely into a single disk read, so reading
both the index AND the table is going to be twice as expensive.

> Why are some table-names and some column-names surrounded by ' " '?
> Are they threated as text-columns?

They are either names generated by postgres ("outer" and "inner") or
field names which are also reserved words in SQL ("key"). You can always
use double quotes around a field name - you have to in some cases if
they are reserved words, and always if they contain "special characters"
(not sure from memory exactly which these are - at least spaces). I
recommend not to use either of these, even if a reserved word is the
best description of your field.

Postgres seems to be a bit better than some other dbms's in allowing
unquoted reserved words as field names if there is no ambiguity. Thsis
may mean that you get a problem if your application is ever ported to a
different dbms.

> I have to admit that the tables are just filled with test-data so the
> analyzer may take just a very simple way since almost no data is in...

Try loading your tables with a realistic number of customers, and you
should see a change in the query plan to use your precious indexes.

/Nis