pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Cc: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
Subject: Re: Bad query optimizer misestimation because of TOAST tables
Date: Wed, 02 Feb 2005 12:44:32 -0500
Message-ID: <188.1107366272@sss.pgh.pa.us> (raw)
In-Reply-To: <42010C24.90301@logi-track.com>
References: <42010C24.90301@logi-track.com>

Markus Schaber <schabios@logi-track.com> writes:
> IMHO, this tells the reason. The query planner has a table size of 3
> pages, which clearly is a case for a seqscan. But during the seqscan,
> the database has to fetch an additional amount of 8225 toast pages and
> 127 toast index pages, and rebuild the geometries contained therein.

I don't buy this analysis at all.  The toasted columns are not those in
the index (because we don't support out-of-line-toasted index entries),
so a WHERE clause that only touches indexed columns isn't going to need
to fetch anything from the toast table.  The only stuff it would fetch
is in rows that passed the WHERE and need to be returned to the client
--- and those costs are going to be the same either way.

I'm not entirely sure where the time is going, but I do not think you
have proven your theory about it.  I'd suggest building the backend
with -pg and getting some gprof evidence.

			regards, tom lane



view thread (7+ messages)  latest in thread

Message-ID: <188.1107366272@sss.pgh.pa.us>
Permalink:  ../188.1107366272@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/188.1107366272@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, postgis-users@postgis.refractions.net
  Subject: Re: Bad query optimizer misestimation because of TOAST tables
  In-Reply-To: <188.1107366272@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox