Il Thursday 25 October 2007 13:20:40 Gregory Stark ha scritto:
> "Gregory Stark" <stark@enterprisedb.com> writes:
> > "Reg Me Please" <regmeplease@gmail.com> writes:
> >> -> Seq Scan on tt_elem (cost=0.00..29.40 rows=1940
> >> width=8) (actual time=0.012..0.013 rows=1 loops=1)
> >
> > The discrepancy etween the estimated rows and actual rows makes me think
> > you've not analyzed this table in a long time. It's probably best to
> > analyze the whole database to have a consistent set of statistics and to
> > catch any other old table stats.
> >
> > There could be other misestimations based due to Postgres limitations but
> > first fix the out of date stats and re-post both plans.
>
> Actually it's pretty clear there are some other bad estimations as well.
> You should send along the view definition too.
>
> And I would recommend you try it with a normal JOIN ON/USING instead of the
> NATURAL JOIN. It's possible it's joining on some unexpected columns --
> though that doesn't really look like it's the case here.
I'm not sure whether my previous message has reached the list.
In any case, the tables have been created with a pg_restore and, thus,
not much stats should be available not out-of-date ones.
I'd actually like to better understand how to compose queries (and indexes)
in order to make them appealing to the query planner.
--------
Oggetto: Re: [PGSQL v8.2.5] Similar queries behave differently
Data: giovedì 25 ottobre 2007
Da: Reg Me Please <regmeplease@gmail.com>
A: pgsql-general@postgresql.org
Hai all again.
Maybe I've solved the problem, but would like to have some hint on "why".
In the second query I've substituted the last join (natural join tt_rice)
with an additional "where condition". I can do this as I am sure that
the tt_rice table will always contain just one row with one field.
The main difference with the first query is that in the first case the
single row with a single field is a "bigint", while in the second one it
is "text".
Otherwise the two queries are almost identical, apart the number of result
rows and the size of the joined tables.
Is there any deeper tutorial on how to read (and understand) the explain
analyze output?
Many thanks again.
