From: Mark Roberts <mailing_lists@pandapocket.com>
To: tv@fuzzy.cz
Cc: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance and IN clauses
Date: Tue, 18 Nov 2008 15:29:25 -0800
Message-ID: <1227050965.30763.7754.camel@localhost> (raw)
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>
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
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: mailing_lists@pandapocket.com, tv@fuzzy.cz, matthew@flymine.org
Subject: Re: Performance and IN clauses
In-Reply-To: <1227050965.30763.7754.camel@localhost>
* 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