pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
optimizing 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