PostgreSQL and limit

-
Many times I have came across this with PostgreSQL if an aggregate like sum, count function is used on a single column of the table the limit clause makes the query real faster. Just try this select count(*) from my_table limit 1; This would be more faster than just select count(*) from my_table; The reason limits decide the scan of data pages and reduces the I/O for the interface program (in my Case psql). What do you people say.

Comments

Anonymous said…
I tried this out on PostgreSQL 7.4.6 with a table that has more than 1 million rows.

W/O limit: 7,324.890 ms
With limit: 7,366.951 ms

With a difference of less than 50 ms I'd say that there is virtually no difference between these two. Either you are running this on a table with a lot of columns or perhaps a newer version of PostgreSQL (8.0?). I'm not sure that either of those would make a difference though.

Joseph Scott
http://joseph.randomnetworks.com/
Thursday, 16 December, 2004
ambitious me said…
I tried this out on PostgreSQL 7.4.6 with a table that has more than 1 million rows.

W/O limit: 7,324.890 ms
With limit: 7,366.951 ms

With a difference of less than 50 ms I'd say that there is virtually no difference between these two. Either you are running this on a table with a lot of columns or perhaps a newer version of PostgreSQL (8.0?). I'm not sure that either of those would make a difference though.

Joseph Scott
http://joseph.randomnetworks.com/

Joseph you are right as you would have seen the select query in my case is having a * i.e all columns targeted
this way I am alreadr increasing the job of PostgreSQL to select for all columns in the table.
But if I use a query something like

select col_1,col_2 from col_table limit 1;

then on targeting the columns I am itself optimizing the query and reducing time in this case LIMIT does not show any improvements.
Thursday, 16 December, 2004
Post a Comment

Popular posts from this blog

Language

-
I wrote this poem at a time when I was struggling for answers in my life. These questions are so intricate that it would fail great scholars and most learned of all and no book would talk about it. Then you turn to the supreme being and ask your questions silence comes in reply. You wait and wait more for an answer and then you are agitated and start complaining. Why does he not answer ? This leads to a moment of introspection and a spark arises that make you wonder of  the language to converse with almighty , Its then That I say .... . Talking to you is a problem to me, As I don't know the language of thee, English or Hindi wonder I in silence of me, Do these language pleases you my lee, No! Says the creator of me, Nor do I listen to Hindi or to English, I listen to thy heart beat in silence of me, So let your heart beat from lup-dup lup-dup to love-love love-love.
Read more

Could Russian and Venezuelan navy joint exercise, speed-up the slowing world economy.

-
You may call it hypothesis or upcoming fact. Collectively world war II and Cold war are known for an era when most inventions and innovations materialized. This was the time when money was pumped into various projects, just to keep the name of nation. Classic example would be space exploration. Not only this the same era called for heavy investments into research and development at defense front. Later defense projects found way into civilian life. Isn't computers network result of such project at DoD. Hope Russians splashing water in America's backyard. Would trigger another cold war. That would lead to chain reaction of funding defense, or may be implementation of missile umbrella on America. Thus Americans pumping more money into market which may lead to easy cash availability. This might give an impetus to slowing world economy.
Read more

Expressing gratitude

-
Never forget and lose a chance to thank people who have actually helped one to rise, with this though I thanked LINUX For You people today, these people acknowledged and published about SaiPACS , which indeed became a life time achievement for me. With lord's wish and will I got the thought of thanking these people. Today I feel relieved after expressing gratitude to Ms. and her team for all they have done for me , though it was her greatness that she found sense of duty in all they have done for me and Linux community.
Read more