pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimizing large query with IN (...)
10+ messages / 7 participants
[nested] [flat]
* optimizing large query with IN (...)
@ 2004-03-10 03:35 Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 08:13 ` Re: optimizing large query with IN (...) Eric Jain <Eric.Jain@isb-sib.ch>
2004-03-10 14:42 ` Re: optimizing large query with IN (...) Steve Atkins <steve@blighty.com>
2004-03-10 16:47 ` Re: optimizing large query with IN (...) scott.marlowe <scott.marlowe@ihs.com>
2004-03-11 00:41 ` Re: optimizing large query with IN (...) Joseph Shraibman <jks@selectacast.net>
0 siblings, 4 replies; 10+ messages in thread
From: Marcus Andree S. Magalhaes @ 2004-03-10 03:35 UTC (permalink / raw)
To: pgsql-performance
Guys,
I got a Java program to tune. It connects to a 7.4.1 postgresql server
running Linux using JDBC.
The program needs to update a counter on a somewhat large number of
rows, about 1200 on a ~130k rows table. The query is something like
the following:
UPDATE table SET table.par = table.par + 1
WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
This query runs on a transaction (by issuing a call to
setAutoCommit(false)) and a commit() right after the query
is sent to the backend.
The process of committing and updating the values is painfully slow
(no surprises here). Any ideas?
Thanks.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
@ 2004-03-10 08:13 ` Eric Jain <Eric.Jain@isb-sib.ch>
2004-03-10 08:38 ` Re: optimizing large query with IN (...) Christopher Kings-Lynne <chriskl@familyhealth.com.au>
3 siblings, 1 reply; 10+ messages in thread
From: Eric Jain @ 2004-03-10 08:13 UTC (permalink / raw)
To: pgsql-performance
> UPDATE table SET table.par = table.par + 1
> WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
How fast is the query alone, i.e.
SELECT * FROM table
WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 08:13 ` Re: optimizing large query with IN (...) Eric Jain <Eric.Jain@isb-sib.ch>
@ 2004-03-10 08:38 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 10+ messages in thread
From: Christopher Kings-Lynne @ 2004-03-10 08:38 UTC (permalink / raw)
To: Eric Jain <Eric.Jain@isb-sib.ch>; +Cc: pgsql-performance
>>UPDATE table SET table.par = table.par + 1
>>WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
>
>
> How fast is the query alone, i.e.
>
> SELECT * FROM table
> WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
Also, post the output of '\d table' and EXPLAIN ANALYZE UPDATE...
Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
@ 2004-03-10 14:42 ` Steve Atkins <steve@blighty.com>
3 siblings, 0 replies; 10+ messages in thread
From: Steve Atkins @ 2004-03-10 14:42 UTC (permalink / raw)
To: pgsql-performance
On Wed, Mar 10, 2004 at 12:35:15AM -0300, Marcus Andree S. Magalhaes wrote:
> Guys,
>
> I got a Java program to tune. It connects to a 7.4.1 postgresql server
> running Linux using JDBC.
>
> The program needs to update a counter on a somewhat large number of
> rows, about 1200 on a ~130k rows table. The query is something like
> the following:
>
> UPDATE table SET table.par = table.par + 1
> WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
>
> This query runs on a transaction (by issuing a call to
> setAutoCommit(false)) and a commit() right after the query
> is sent to the backend.
>
> The process of committing and updating the values is painfully slow
> (no surprises here). Any ideas?
I posted an analysis of use of IN () like this a few weeks ago on
pgsql-general.
The approach you're using is optimal for < 3 values.
For any more than that, insert value1 ... value1200 into a temporary
table, then do
UPDATE table SET table.par = table.par + 1
WHERE table.key IN (SELECT value from temp_table);
Indexing the temporary table marginally increases the speed, but not
significantly.
Cheers,
Steve
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
@ 2004-03-10 16:47 ` scott.marlowe <scott.marlowe@ihs.com>
2004-03-10 17:02 ` Re: optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-12 17:39 ` Re: optimizing large query with IN (...) Josh Berkus <josh@agliodbs.com>
3 siblings, 2 replies; 10+ messages in thread
From: scott.marlowe @ 2004-03-10 16:47 UTC (permalink / raw)
To: Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>; +Cc: pgsql-performance
On Wed, 10 Mar 2004, Marcus Andree S. Magalhaes wrote:
>
> Guys,
>
> I got a Java program to tune. It connects to a 7.4.1 postgresql server
> running Linux using JDBC.
>
> The program needs to update a counter on a somewhat large number of
> rows, about 1200 on a ~130k rows table. The query is something like
> the following:
>
> UPDATE table SET table.par = table.par + 1
> WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
>
> This query runs on a transaction (by issuing a call to
> setAutoCommit(false)) and a commit() right after the query
> is sent to the backend.
>
> The process of committing and updating the values is painfully slow
> (no surprises here). Any ideas?
The problem, as I understand it, is that 7.4 introduced massive
improvements in handling moderately large in() clauses, as long as they
can fit in sort_mem, and are provided by a subselect.
So, creating a temp table with all the values in it and using in() on the
temp table may be a win:
begin;
create temp table t_ids(id int);
insert into t_ids(id) values (123); <- repeat a few hundred times
select * from maintable where id in (select id from t_ids);
...
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 16:47 ` Re: optimizing large query with IN (...) scott.marlowe <scott.marlowe@ihs.com>
@ 2004-03-10 17:02 ` Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 17:11 ` Re: optimizing large query with IN (...) Steve Atkins <steve@blighty.com>
2004-03-10 17:51 ` Re: optimizing large query with IN (...) scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 2 replies; 10+ messages in thread
From: Marcus Andree S. Magalhaes @ 2004-03-10 17:02 UTC (permalink / raw)
To: scott.marlowe@ihs.com; +Cc: marcus.magalhaes@vlinfo.com.br; pgsql-performance
Hmm... from the 'performance' point of view, since the data comes from
a quite complex select statement, Isn't it better/quicker to have this
select replaced by a select into and creating a temporary database?
> The problem, as I understand it, is that 7.4 introduced massive
> improvements in handling moderately large in() clauses, as long as they
> can fit in sort_mem, and are provided by a subselect.
>
> So, creating a temp table with all the values in it and using in() on
> the temp table may be a win:
>
> begin;
> create temp table t_ids(id int);
> insert into t_ids(id) values (123); <- repeat a few hundred times
> select * from maintable where id in (select id from t_ids);
> ...
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 16:47 ` Re: optimizing large query with IN (...) scott.marlowe <scott.marlowe@ihs.com>
2004-03-10 17:02 ` Re: optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
@ 2004-03-10 17:11 ` Steve Atkins <steve@blighty.com>
1 sibling, 0 replies; 10+ messages in thread
From: Steve Atkins @ 2004-03-10 17:11 UTC (permalink / raw)
To: pgsql-performance
On Wed, Mar 10, 2004 at 02:02:23PM -0300, Marcus Andree S. Magalhaes wrote:
> Hmm... from the 'performance' point of view, since the data comes from
> a quite complex select statement, Isn't it better/quicker to have this
> select replaced by a select into and creating a temporary database?
Definitely - why loop the data into the application and back out again
if you don't need to?
> > The problem, as I understand it, is that 7.4 introduced massive
> > improvements in handling moderately large in() clauses, as long as they
> > can fit in sort_mem, and are provided by a subselect.
> >
> > So, creating a temp table with all the values in it and using in() on
> > the temp table may be a win:
> >
> > begin;
> > create temp table t_ids(id int);
> > insert into t_ids(id) values (123); <- repeat a few hundred times
> > select * from maintable where id in (select id from t_ids);
> > ...
Cheers,
Steve
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 16:47 ` Re: optimizing large query with IN (...) scott.marlowe <scott.marlowe@ihs.com>
2004-03-10 17:02 ` Re: optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
@ 2004-03-10 17:51 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 10+ messages in thread
From: scott.marlowe @ 2004-03-10 17:51 UTC (permalink / raw)
To: Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>; +Cc: pgsql-performance
I'm not sure exactly what you're saying here. If the data in the in()
clause comes from a complex select, then just use the select in there, and
bypass the temporary table idea.
I'm not sure what a temporary database is, did you mean temporary table?
if so, then my above comment addresses that point.
On Wed, 10 Mar 2004, Marcus Andree S. Magalhaes wrote:
>
> Hmm... from the 'performance' point of view, since the data comes from
> a quite complex select statement, Isn't it better/quicker to have this
> select replaced by a select into and creating a temporary database?
>
>
>
> > The problem, as I understand it, is that 7.4 introduced massive
> > improvements in handling moderately large in() clauses, as long as they
> > can fit in sort_mem, and are provided by a subselect.
> >
> > So, creating a temp table with all the values in it and using in() on
> > the temp table may be a win:
> >
> > begin;
> > create temp table t_ids(id int);
> > insert into t_ids(id) values (123); <- repeat a few hundred times
> > select * from maintable where id in (select id from t_ids);
> > ...
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 16:47 ` Re: optimizing large query with IN (...) scott.marlowe <scott.marlowe@ihs.com>
@ 2004-03-12 17:39 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2004-03-12 17:39 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>; +Cc: pgsql-performance
Marcus,
> The problem, as I understand it, is that 7.4 introduced massive
> improvements in handling moderately large in() clauses, as long as they
> can fit in sort_mem, and are provided by a subselect.
Also, this problem may be fixed in 7.5, when it comes out. It's a known
issue.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: optimizing large query with IN (...)
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
@ 2004-03-11 00:41 ` Joseph Shraibman <jks@selectacast.net>
3 siblings, 0 replies; 10+ messages in thread
From: Joseph Shraibman @ 2004-03-11 00:41 UTC (permalink / raw)
To: Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
Marcus Andree S. Magalhaes wrote:
> Guys,
>
> I got a Java program to tune. It connects to a 7.4.1 postgresql server
> running Linux using JDBC.
>
> The program needs to update a counter on a somewhat large number of
> rows, about 1200 on a ~130k rows table. The query is something like
> the following:
>
> UPDATE table SET table.par = table.par + 1
> WHERE table.key IN ('value1', 'value2', ... , 'value1200' )
>
How often do you update this counter? Each update requires adding a new
row to the table and invalidating the old one. Then the old ones stick
around until the next vacuum.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-03-12 17:39 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-10 03:35 optimizing large query with IN (...) Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 08:13 ` Eric Jain <Eric.Jain@isb-sib.ch>
2004-03-10 08:38 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-03-10 14:42 ` Steve Atkins <steve@blighty.com>
2004-03-10 16:47 ` scott.marlowe <scott.marlowe@ihs.com>
2004-03-10 17:02 ` Marcus Andree S. Magalhaes <marcus.magalhaes@vlinfo.com.br>
2004-03-10 17:11 ` Steve Atkins <steve@blighty.com>
2004-03-10 17:51 ` scott.marlowe <scott.marlowe@ihs.com>
2004-03-12 17:39 ` Josh Berkus <josh@agliodbs.com>
2004-03-11 00:41 ` Joseph Shraibman <jks@selectacast.net>
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