On 10/25/07, Reg Me Please <regmeplease@gmail.com> wrote:
> 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.
I'm not sure you understand stats in pgsql. The planner makes
decsisions based on those stats, expecting them to be up to date.
with default or out of date stats, the planner cannot make a good
decision.
Run analyze on your db, rerun the explain analyze and post the output.
without good stats, you can't make a query that's guaranteed to work
well, because pgsql is simply guessing about your data distribution.
