pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
indexes make other queries slow!
3+ messages / 3 participants
[nested] [flat]

* indexes make other queries slow!
@ 2004-09-16 21:20  Qing Zhao <qzhao@supplyfx.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Qing Zhao @ 2004-09-16 21:20 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh:

Sorry for the reply to the existing subject!

The newly added indexes have made all other queries much slower except 
the uploading ops.
As a result, all the CPU's are running crazy but not much is getting 
finished and our Application
Server waits for certain time and then times out. Customers thought the 
system hung.

My guess is that all the queries that involves the columns  that are 
being indexed need to
be rewritten to use the newly created indexes to avoid the performance 
issues. The reason
is that REINDEX does not help either. Does it make sense?

Thanks!

Qing

On Sep 16, 2004, at 2:05 PM, Josh Berkus wrote:

> Qing,
>
> Please don't start a new question by replying to someone else's 
> e-mail.   It
> confuses people and makes it unlikely for you to get help.
>
>> My suspicion is that since  now a few indexes are added, every ops are
>> run by PostgreSQL with the indexes being used when calculating cost.
>> This leads to the downgrade of performance.
>
> That seems rather unlikely to me.    Unless you've *really* complex 
> queries
> and some unusual settings, you can't swamp the CPU through query 
> planning.
>
> On the other hand, your mention of REINDEX indicates that the table is 
> being
> updated very frequently.   If that's the case, then the solution is 
> probably
> for you to cut back on the number of indexes.
>
> -- 
> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: indexes make other queries slow!
@ 2004-09-16 21:24  Josh Berkus <josh@agliodbs.com>
  parent: Qing Zhao <qzhao@supplyfx.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2004-09-16 21:24 UTC (permalink / raw)
  To: Qing Zhao <qzhao@supplyfx.com>; +Cc: pgsql-performance

Qing,

> My guess is that all the queries that involves the columns  that are
> being indexed need to
> be rewritten to use the newly created indexes to avoid the performance
> issues. The reason
> is that REINDEX does not help either. Does it make sense?

What's the rate of updates on the newly indexed tables?    If you have a lot 
of updates, the work that the database does to keep the indexes current would 
put a big load on your server.   This is far more likely to be the cause of 
your issues.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: indexes make other queries slow!
@ 2004-09-16 21:36  Jason Coene <jcoene@gotfrag.com>
  parent: Qing Zhao <qzhao@supplyfx.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Jason Coene @ 2004-09-16 21:36 UTC (permalink / raw)
  To: 'Qing Zhao' <qzhao@supplyfx.com>; +Cc: pgsql-performance

> My guess is that all the queries that involves the columns  that are
> being indexed need to
> be rewritten to use the newly created indexes to avoid the performance
> issues. The reason
> is that REINDEX does not help either. Does it make sense?
> 

Qing,

Generally, adding new indexes blindly will hurt performance, not help it.

More indexes mean more work during INSERT/UPDATE.  That could easily be
hampering your performance if you have a high INSERT/UPDATE volume.

Run your queries through EXPLAIN ANALYZE to make sure they're using the
right indexes.  Take a look at the pg_stat_user_indexes table to see what
indexes are simply not being used.

Jason




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-09-16 21:36 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-16 21:20 indexes make other queries slow! Qing Zhao <qzhao@supplyfx.com>
2004-09-16 21:24 ` Josh Berkus <josh@agliodbs.com>
2004-09-16 21:36 ` Jason Coene <jcoene@gotfrag.com>

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