Re: Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL

Andrew McMillan wrote:

> mlw wrote:
> >
> > My music database has 50,000 arises and 210,000 albums. Many artists
> > have only one or 2 entries in the albums table (for the youngsters, CD
> > table ;-). About 34,000 have the integer key for "Various Artists" as
> > their artist entry, and another few thousand have things like "Movie
> > Soundtrack" and so on.
> >
> > When the statistics are computed, these relatively few records with a
> > huge number of relations distort the statistics and make it impossible
> > to get postgres to use an index on that table without the -fs switch.
> >
> > This is bad because it always forces use of an index, even when postgres
> > would legitimately ignore it.
>
> What about doing:
> SET enable_seqscan TO 'Off';
> Just before the query in question?
>
> That way you'd only affect the single query. Possibly you could even
> code to spot the two aberrant situations and not do it in those ones.

I'd rather not pollute the application's SQL with postgres-isms. Not that I
don't love postgres, but there are always critics looking for a reason to use
Oracle or (gasp) MS-SQL.

As for "code to spot.." I am fairly new to hacking postgres. (Though, I have
been using it in various projects since ~1995), but I am excellent C/C++ guy,
give me a pointer to where (a) statistics are calculated, and (b) where they
are interpreted, and I would do that.

Just a question, however, what is the feeling about the way statistics are
currently being calculated? My feeling is that some sort of windowing
algorithm be used to normalize the statistics to the majority of the entries
in a table. It could be as simple as discarding the upper and lower 10% of
the record stats, and use the remaining 80% for statistics. That would
certainly take care of my problem (and others I am sure), and I'd be glad to
write it. ;-)

>
>
> Regards,
> Andrew.
> --
> _____________________________________________________________________
> Andrew McMillan, e-mail: Andrew(at)cat-it(dot)co(dot)nz
> Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
> Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267