Re: 7.0.2 issues / Geocrawler

"Ross J. Reedstrom" wrote:
>
> On Wed, Jul 12, 2000 at 06:17:23AM -0700, Tim Perdue wrote:
> > Mike Mascari wrote:
> > > Have you VACUUM ANALYZE'd the table(s) in question?
> >
> > Yes, they've been vacuum analyze'd and re-vaccum analyze'd to death.
> > Also added some extra indexes that I don't really need just to see if
> > that helps.
>
> Tim, why are you building a multikey index, especially one containing a
> large text field? It's almost never a win to index a text field, unless
> all the WHERE clauses that use it are either anchored to the beginning
> of the field, or are equality tests (in which case, the field is really
> an enumerated type, masquerading as a text field)
>
> A multikey index is only useful for a very limited set of queries. Here's
> a message from last August, where Tom Lane talks about that:
>
> http://www.postgresql.org/mhonarc/pgsql-sql/1999-08/msg00145.html

I think Tim had 2 problems. The first was tuples whose text
attributes did not permit two on the same index page. The second,
however, is that a query against the *same schema* under 6.x now
runs slower by a factor of 15 under 7.x:

"The following query is at the very heart of the site and it
takes
upwards of 15-20 seconds to run now. It used to be instantaneous.

explain SELECT mailid, mail_date, mail_is_followup, mail_from,
mail_subject
FROM mail_archive WHERE mail_list=35 AND mail_year=2000
AND mail_month=1 ORDER BY mail_date DESC LIMIT 26 OFFSET 0;

NOTICE: QUERY PLAN:

Sort (cost=138.41..138.41 rows=34 width=44)
-> Index Scan using idx_mail_archive_list_yr_mo on
tbl_mail_archive
(cost=0.00..137.55 rows=34 width=44)

EXPLAIN"

Even though he's using a mult-key index here, it is composed
entirely of integer fields. Its reducing to a simple index scan +
sort, so I don't see how the performance could drop off so
dramatically. Perhaps if we could see the EXPLAIN output with the
same query against the 6.x database we could see what's going on.

Mike Mascari