Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to read a query plan?

How to read a query plan?

From
elwood@agouros.de (Konstantinos Agouros)
Date:
Hi,

since I am still working on my quite big database and the last time a simple
index saved my life I would like to understand query plans. Here are two
one without and one with index I see different numbers, but would like to know
what they mean:
logs=> explain select distinct on (url) url, category, action from websenseflat where webhost='dir.yahoo.com' and datum
>='1-26-2001'and datum < '1-27-2001'; 
NOTICE:  QUERY PLAN:

Unique  (cost=0.01..0.01 rows=1 width=36)
  ->  Sort  (cost=0.01..0.01 rows=1 width=36)
        ->  Seq Scan on websenseflat  (cost=0.00..0.00 rows=1 width=36)

EXPLAIN

after this I created an index on the field webhost, now the query plan says:

NOTICE:  QUERY PLAN:

Unique  (cost=19690.08..19690.22 rows=5 width=36)
  ->  Sort  (cost=19690.08..19690.08 rows=53 width=36)
        ->  Index Scan using wsflwebhost on websenseflat  (cost=0.00..19688.55 rows=53 width=36)

EXPLAIN

Could someone give me an interpretation (or a URL that explains this)?

Thanks,

Konstantin
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres

Re: How to read a query plan?

From
Tom Lane
Date:
elwood@agouros.de (Konstantinos Agouros) writes:
> after this I created an index on the field webhost, now the query plan says:

CREATE INDEX updates the basic statistics about the table's size (number
of rows and disk blocks).  The first plan was evidently generated using
statistics that claimed the table was empty.  The second plan has rather
more plausible-looking numbers.  I surmise that you did something like

    CREATE TABLE ...

    VACUUM   (sets stats to reflect empty table)

    load a bunch of data

    EXPLAIN

    CREATE INDEX  (now stats reflect existence of data)

    EXPLAIN

> Could someone give me an interpretation (or a URL that explains this)?

See the performance section of the User's Guide for a general discussion
of EXPLAIN.

            regards, tom lane

Re: How to read a query plan?

From
Peter Eisentraut
Date:
Konstantinos Agouros writes:

> logs=> explain select distinct on (url) url, category, action from websenseflat where webhost='dir.yahoo.com' and
datum>='1-26-2001' and datum < '1-27-2001'; 
> NOTICE:  QUERY PLAN:
>
> Unique  (cost=0.01..0.01 rows=1 width=36)
>   ->  Sort  (cost=0.01..0.01 rows=1 width=36)
>         ->  Seq Scan on websenseflat  (cost=0.00..0.00 rows=1 width=36)
>
> EXPLAIN
>
> after this I created an index on the field webhost, now the query plan says:
>
> NOTICE:  QUERY PLAN:
>
> Unique  (cost=19690.08..19690.22 rows=5 width=36)
>   ->  Sort  (cost=19690.08..19690.08 rows=53 width=36)
>         ->  Index Scan using wsflwebhost on websenseflat  (cost=0.00..19688.55 rows=53 width=36)
>
> EXPLAIN
>
> Could someone give me an interpretation (or a URL that explains this)?

http://www.de.postgresql.org/devel-corner/docs/postgres/performance-tips.html

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: How to read a query plan?

From
Konstantinos Agouros
Date:
On Wed, Apr 18, 2001 at 05:31:48PM +0200, Peter Eisentraut wrote:
> Konstantinos Agouros writes:
>
> >
> > Could someone give me an interpretation (or a URL that explains this)?
>
> http://www.de.postgresql.org/devel-corner/docs/postgres/performance-tips.html
Thanks that was the link I needed, just to make sure I understood the
page correctly I want to get cost down?

Konstantin
>
> --
> Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter
>

--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres