Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 70E7D2EBA7B for ; Thu, 24 Nov 2011 13:20:45 -0400 (AST) Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RTcya-0005Cx-Gy for pgsql-performance@postgresql.org; Thu, 24 Nov 2011 17:20:45 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1RTcyN-0005fv-6g for pgsql-performance@postgresql.org; Thu, 24 Nov 2011 09:20:31 -0800 Date: Thu, 24 Nov 2011 09:20:31 -0800 (PST) From: MirrorX To: pgsql-performance@postgresql.org Message-ID: <1322155231200-5020742.post@n5.nabble.com> Subject: query uses index but takes too much time? MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 201111/266 X-Sequence-Number: 45777 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.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.