Message-ID: <44620083.10503@superlativ.dk>
Date: Wed, 10 May 2006 17:02:27 +0200
From: Nis Jorgensen <nis@superlativ.dk>
User-Agent: Opera/7.5 (Windows; U; Longhorn; en-US; rv:1.0.0) 
MIME-Version: 1.0
To: Clemens Eisserer <linuxhippy@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Question about explain-command...
References: <194f62550605100449p2d200fc3pfe501da895cfcc00@mail.gmail.com>
In-Reply-To: <194f62550605100449p2d200fc3pfe501da895cfcc00@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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