How to read a query plan?
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
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
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
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