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