From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Stone <mstone+postgres@mathom.us>
Cc: pgsql-performance@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Subject: Re: Index scan startup time
Date: Thu, 30 Mar 2006 09:25:57 -0500
Message-ID: <14358.1143728757@sss.pgh.pa.us> (raw)
In-Reply-To: <20060330132915.GL6811@mathom.us>
References: <200603301359.11038.peter_e@gmx.net>
<20060330120206.GA13075@uio.no>
<200603301423.54510.peter_e@gmx.net>
<20060330123134.GA13408@uio.no>
<20060330124251.GK6811@mathom.us>
<20060330125147.GA13611@uio.no>
<20060330132915.GL6811@mathom.us>
Michael Stone <mstone+postgres@mathom.us> writes:
> Yes. I was looking at the other side; I thought pg could estimate how
> much work it would have to do to hit the limit, but double-checking it
> looks like it can't.
Yes, it does, you just have to understand how to interpret the EXPLAIN
output. Peter had
Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1)
-> Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1)
Filter: ((state = 10000) OR (state = 10001))
Total runtime: 207357.000 ms
Notice that the total cost of the LIMIT node is estimated as far less
than the total cost of the IndexScan node. That's exactly because the
planner doesn't expect the indexscan to run to completion.
The problem here appears to be a non-random correlation between state
and activity, such that the desired state values are not randomly
scattered in the activity sequence. The planner doesn't know about that
correlation and hence can't predict the poor startup time.
regards, tom lane
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, mstone+postgres@mathom.us, peter_e@gmx.net
Subject: Re: Index scan startup time
In-Reply-To: <14358.1143728757@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