pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
query uses index but takes too much time? 3+ messages / 3 participants [nested] [flat]
* query uses index but takes too much time? @ 2011-11-24 17:20 MirrorX <mirrorx@gmail.com> 0 siblings, 2 replies; 3+ messages in thread From: MirrorX @ 2011-11-24 17:20 UTC (permalink / raw) To: pgsql-performance hello to all, i would like your advice on the following matter. i have a table with 150 million rows. there are some indexes on this table but the one that is really important is one that has 3 columns (a,b,c). one application constantly makes queries and the query planner uses this index to narrow down the final set of results. so usually from 150 millions, when the 3 conditions have been applied, the remaining rows to be checked are about 20-300. So these queries are very fast, and take from 10-100 ms usually. There is a special case where these 3 conditions narrow down the final set to 15.000 rows so the server must check all these rows. The result is that the query takes around 1 minute to complete. Is that a normal time for the execution of the query? i know that most of you will send me the link with the guide to reporting slow queries but that's not the point at the moment. i am not looking for a specific answer why this is happening. i just want to know if that seems strange to more people than just me and if i should look into that. but if for the above you need to have a clearer picture of the server then: -red hat 5.6 -32 cores, -96GB ram -fiber storage (4GBps) -postgresql 9.0.5 -shared_buffers : 25 GB -not i/o bound (too many disks, different partitions for backup, archives, xlogs, indexes) -not cpu bound (the cpu util was about 5% when i performed the tests) -the query planner values on postgresql.conf are the default -i also performed the tests on the hot-standby with the same results -the query plan is the correct one, indicating that it should use the correct index -i forced index_scan to off and then it used bitmap heap scan with similar results. -i forced bitmap heap scan to off and then it did a seq scan any ideas? thx in advance for your insight -- View this message in context: http://postgresql.1045698.n5.nabble.com/query-uses-index-but-takes-too-much-time-tp5020742p5020742.h... Sent from the PostgreSQL - performance mailing list archive at Nabble.com. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: query uses index but takes too much time? @ 2011-11-24 17:51 Aidan Van Dyk <aidan@highrise.ca> parent: MirrorX <mirrorx@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Aidan Van Dyk @ 2011-11-24 17:51 UTC (permalink / raw) To: MirrorX <mirrorx@gmail.com>; +Cc: pgsql-performance On Thu, Nov 24, 2011 at 12:20 PM, MirrorX <mirrorx@gmail.com> wrote: > -32 cores, > -not cpu bound (the cpu util was about 5% when i performed the tests) A single query will only use a single CPU. 5% of 32 cores is 100% of 1.6 cores. Are you sure that the 1 core doing the 1 postgresql query wasn't 100% utilized? a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: query uses index but takes too much time? @ 2011-11-24 19:24 Cédric Villemain <cedric.villemain.debian@gmail.com> parent: MirrorX <mirrorx@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Cédric Villemain @ 2011-11-24 19:24 UTC (permalink / raw) To: MirrorX <mirrorx@gmail.com>; +Cc: pgsql-performance Le 24 novembre 2011 18:20, MirrorX <mirrorx@gmail.com> a écrit : > hello to all, > > i would like your advice on the following matter. i have a table with 150 > million rows. there are some indexes on this table but the one that is > really important is one that has 3 columns (a,b,c). one application > constantly makes queries and the query planner uses this index to narrow > down the final set of results. so usually from 150 millions, when the 3 > conditions have been applied, the remaining rows to be checked are about > 20-300. So these queries are very fast, and take from 10-100 ms usually. > There is a special case where these 3 conditions narrow down the final set > to 15.000 rows so the server must check all these rows. The result is that > the query takes around 1 minute to complete. Is that a normal time for the > execution of the query? > > i know that most of you will send me the link with the guide to reporting > slow queries but that's not the point at the moment. i am not looking for a > specific answer why this is happening. > i just want to know if that seems strange to more people than just me and if > i should look into that. > > but if for the above you need to have a clearer picture of the server then: > -red hat 5.6 > -32 cores, > -96GB ram > -fiber storage (4GBps) > -postgresql 9.0.5 > -shared_buffers : 25 GB > -not i/o bound (too many disks, different partitions for backup, archives, > xlogs, indexes) > -not cpu bound (the cpu util was about 5% when i performed the tests) > -the query planner values on postgresql.conf are the default > -i also performed the tests on the hot-standby with the same results > -the query plan is the correct one, indicating that it should use the > correct index > -i forced index_scan to off and then it used bitmap heap scan with similar > results. > -i forced bitmap heap scan to off and then it did a seq scan > > any ideas? thx in advance for your insight > not it is not that strange. It can be several things that lead you to this situation. > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/query-uses-index-but-takes-too-much-time-tp5020742p5020742.h... > Sent from the PostgreSQL - performance mailing list archive at Nabble.com. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-24 19:24 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-11-24 17:20 query uses index but takes too much time? MirrorX <mirrorx@gmail.com> 2011-11-24 17:51 ` Aidan Van Dyk <aidan@highrise.ca> 2011-11-24 19:24 ` Cédric Villemain <cedric.villemain.debian@gmail.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