Thread: query uses index but takes too much time?

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.

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.

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.html
> 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