Subject: Re: Performance and IN clauses
From: Mark Roberts <mailing_lists@pandapocket.com>
To: tv@fuzzy.cz
Cc: Matthew Wakeling <matthew@flymine.org>, pgsql-performance@postgresql.org
In-Reply-To: <64433.89.102.139.23.1227026325.squirrel@sq.gransy.com>
References: <c2350ba40811180753m2c6b698csdf180047e0fa621f@mail.gmail.com>
	<alpine.DEB.1.10.0811181610310.4666@aragorn.flymine.org>
	<64433.89.102.139.23.1227026325.squirrel@sq.gransy.com>
Content-Type: text/plain
Date: Tue, 18 Nov 2008 15:29:25 -0800
Message-Id: <1227050965.30763.7754.camel@localhost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Tue, 2008-11-18 at 17:38 +0100, tv@fuzzy.cz wrote:
> I bet there is no 'critical' length - this is just another case of
> index
> scan vs. seqscan. The efficiency depends on the size of the table /
> row,
> amount of data in the table, variability of the column used in the IN
> clause, etc.
> 
> Splitting the query with 1000 items into 10 separate queries, the
> smaller
> queries may be faster but the total time consumed may be actually
> higher.
> Something like
> 
> 10 * (time of small query) + (time to combine them) > (time of large
> query)
> 
> If the performance of the 'split' solution is actually better than the
> original query, it just means that the planner does not use index scan
> when it actually should. That means that either
> 
> (a) the planner is not smart enough
> (b) it has not current statistics of the table (run ANALYZE on the
> table)
> (c) the statistics are not detailed enough (ALTER TABLE ... SET
> STATICTICS)
> (d) the cost variables are not set properly (do not match the hardware
> -
> decreate index scan cost / increase seq scan cost)
> 
> regards
> Tomas

I know that it's much faster (for us) to run many smaller queries than
one large query, and I think that it's primarily because of your reason
a.  Most of our problems come from Pg misunderstanding the results of a
join and making a bad plan decision.  Batching dramatically reduces the
liklihood of this.

-Mark