PostgreSQL性能相关的必知SQL

创建索引

索引的类型,有B-tree, Hash, GiST, SP-GiST和GIN。对于这些type,使用如下语句创建索引:

执行计划

使用EXPLAIN命令来显示SQL的执行计划,在pg中,EXPLAIN的命令格式如下:

option可以为ANALYZE/VERBOSE/COSTS/BUFFERS/FORMAT

若使用ANALYZE,SQL语句会真的被执行,因此可以看到实际花费的时间,以及获得的行数。所以如果不希望真的修改数据,需要记得加上ROLLBACK。FORMAT则制定输出格式,支持TEXT/XML/JSON/YAML。

理解cost

EXPLAIN输出结果中的cost,其中有两个值,中间用..隔开,第一个数字是启动成本,即返回第一行需要的成本,第二个数字表示返回所有数据的成本。而程本cost描述了执行一个SQL的代价是多少,默认情况下不同的操作所带来不同的cost值:

  • 顺序扫描一个数据块,cost为1
  • 随机扫描一个数据块,cost为4
  • 处理一个数据行的cpu,cost为0.01
  • 处理一个索引列的cpu,cost为0.005
  • 每个操作符的cpu,cost为0.0025

下图是加上ANALYZE后的一个示例:

可以看出多了actual time等信息。

查看数据库占用磁盘空间

最简单的写法

然而看bytes数是要命的事情,所以大象提供了人性化的函数pg_size_pretty( ),将bytes转换为human-readable的单位(KB/MB或GB),而且是自动根据大小来判断使用哪种单位。

同样的方法,适用于获取表的磁盘占用量pg_relation_size( ),pg_total_relation_size( )等。

考虑使用copy来迁移数据表

大表的数据导出和导入单表的时候,COPY语句效率相对较高,例如常把数据表导出到csv文件,然后从csv文件导入为另一张表,写法如下:

尽管缺点是显而易见的(只拷贝了table,且from时必须直接插入到表中),但是在大数据量的情况下,copy的性能是明显优于insert或者类似PgJDBC的\(executeBatch()\)。另,根据2ndQuadrant上这篇文章中所描述的测试结果,copy在各类延迟下的综合性能而言是非常不错的选择:

drivers例如libpq、PgJDBC、psycopg2等都支持copy。pg_dump也用到了copy,另外同时可以尝试的导入导出工具包括pg_bulkload和barman等。

114 thoughts on “PostgreSQL性能相关的必知SQL

  1. Emanuel Bugna说道:

    Hello, you used to write wonderful, but the last several posts have been kinda boring?K I miss your tremendous writings. Past few posts are just a little out of track! come on!

  2. m88说道:

    An interesting discussion is worth comment. I think that you should write more on this topic, it might not be a taboo subject but generally people are not enough to speak on such topics. To the next. Cheers

  3. You completed various good points there. I did a search on the matter and found the majority of people will consent with your blog.

  4. I’m often to running a blog and i really respect your content. The article has actually peaks my interest. I am going to bookmark your web site and maintain checking for brand new information.

  5. I like this post, enjoyed this one regards for posting.

  6. Enjoyed reading through this, very good stuff, thankyou.

  7. Great website. A lot of helpful info here. I¦m sending it to a few friends ans also sharing in delicious. And obviously, thanks for your sweat!

  8. breast actives说道:

    hi!,I like your writing so so much! proportion we communicate more approximately your article on AOL? I require a specialist in this house to solve my problem. May be that is you! Having a look ahead to look you.

  9. What i don’t realize is actually how you’re now not actually a lot more neatly-favored than you might be right now. You’re very intelligent. You already know thus considerably in terms of this subject, produced me individually consider it from numerous varied angles. Its like men and women aren’t involved unless it is one thing to do with Girl gaga! Your own stuffs outstanding. At all times handle it up!

  10. This really answered my problem, thank you!

  11. Great wordpress blog here.. It’s hard to find quality writing like yours these days. I really appreciate people like you! take care

  12. Perfectly composed written content, thanks for information .

  13. Amazing! This blog looks just like my old one! It’s on a completely different subject but it has pretty much the same layout and design. Wonderful choice of colors!

  14. Very nice post. I simply stumbled upon your blog and wished to mention that I have truly enjoyed browsing your blog posts. In any case I’ll be subscribing for your rss feed and I hope you write again very soon!

  15. Greetings I am so thrilled I found your blog page, I really found you by mistake, while I was looking on Aol for something else, Anyways I am here now and would just like to say thanks for a remarkable post and a all round thrilling blog (I also love the theme/design), I don’t have time to read it all at the moment but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read much more, Please do keep up the fantastic work.

  16. index说道:

    Some genuinely nice and utilitarian information on this internet site, too I believe the pattern contains excellent features.

  17. Hey there, You have done an incredible job. I’ll definitely digg it and personally recommend to my friends. I am sure they will be benefited from this web site.

  18. Awsome post and right to the point. I am not sure if this is in fact the best place to ask but do you people have any thoughts on where to get some professional writers? Thank you 🙂

  19. Avenere Cladding说道:

    Heya i’m for the first time here. I came across this board and I find It really useful & it helped me out a lot. I hope to give something back and help others like you helped me.

  20. weitere Infos说道:

    Yeah bookmaking this wasn’t a risky decision great post! .

  21. More Help说道:

    I simply want to tell you that I’m all new to blogging and site-building and seriously enjoyed your web-site. Very likely I’m likely to bookmark your blog . You amazingly come with incredible article content. Appreciate it for revealing your blog site.

  22. Bola88 Online说道:

    Hi, Neat post. There’s a problem along with your web site in internet explorer, could check this?K IE nonetheless is the market chief and a big part of folks will omit your excellent writing because of this problem.

  23. Poker88说道:

    Good – I should certainly pronounce, impressed with your website. I had no trouble navigating through all tabs as well as related info ended up being truly easy to do to access. I recently found what I hoped for before you know it in the least. Reasonably unusual. Is likely to appreciate it for those who add forums or anything, web site theme . a tones way for your customer to communicate. Excellent task.

  24. LED Deckenlampe说道:

    Excellent read, I just passed this onto a friend who was doing some research on that. And he just bought me lunch because I found it for him smile Thus let me rephrase that: Thanks for lunch! “He who walks in another’s tracks leaves no footprints.” by Joan Brannon.

  25. Please let me know if you’re looking for a writer for your site. You have some really good articles and I feel I would be a good asset. If you ever want to take some of the load off, I’d really like to write some articles for your blog in exchange for a link back to mine. Please send me an email if interested. Kudos!

  26. Really Appreciate this article, can you make it so I receive an email when there is a fresh post?

  27. Thanks for the sensible critique. Me & my neighbor were just preparing to do some research on this. We got a grab a book from our local library but I think I learned more clear from this post. I’m very glad to see such wonderful info being shared freely out there.

  28. Hi there, You have performed an incredible job. I will certainly digg it and in my view recommend to my friends. I’m confident they will be benefited from this web site.

  29. Definitely, what a great website and informative posts, I will bookmark your blog.Best Regards!

  30. Very interesting info !Perfect just what I was searching for! “Charity is injurious unless it helps the recipient to become independent of it.” by John Davidson Rockefeller, Sr..

  31. world news说道:

    I likewise think hence, perfectly written post! .

  32. Hi! I could have sworn I’ve been to this website before but after reading through some of the post I realized it’s new to me. Anyways, I’m definitely glad I found it and I’ll be book-marking and checking back often!

  33. the general说道:

    I am curious to find out what blog system you are working with? I’m having some small security issues with my latest website and I would like to find something more risk-free. Do you have any solutions?

  34. Heya i’m for the first time here. I found this board and I in finding It truly helpful & it helped me out a lot. I hope to give one thing again and help others such as you aided me.

  35. I like this post, enjoyed this one thanks for putting up. “The goal of revival is conformity to the image of Christ, not imitation of animals.” by Richard F. Lovelace.

  36. Wow, superb blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your web site is excellent, as well as the content!

  37. travel Asia说道:

    You have brought up a very superb points, thankyou for the post.

  38. Catalina Bley说道:

    other and laughed.|TWERKhoreo® is a weekly class held for our advanced twerkers who want to put their moves learnt from TWERKFit® classes into complex routines.|I now have absolutely no focus for the rest of the day simply because

  39. where to sleep说道:

    Heya are using WordPress for your blog platform? I’m new to the blog world but I’m trying to get started and create my own. Do you require any html coding expertise to make your own blog? Any help would be greatly appreciated!

  40. I believe this site has some real wonderful info for everyone :D. “I like work it fascinates me. I can sit and look at it for hours.” by Jerome K. Jerome.

  41. Hello there, You have done an incredible job. I will definitely digg it and personally recommend to my friends. I am sure they will be benefited from this site.

  42. Hi there! Someone in my Myspace group shared this website with us so I came to take a look. I’m definitely loving the information. I’m book-marking and will be tweeting this to my followers! Terrific blog and excellent design and style.

  43. Hey just wanted to give you a brief heads up and let you know a few of the pictures aren’t loading properly. I’m not sure why but I think its a linking issue. I’ve tried it in two different web browsers and both show the same outcome.

  44. I am continuously searching online for articles that can assist me. Thank you!

  45. this isn’t just any ordinary twerking clip.|From Miley Cyrus’s titillating 2013 overall performance to online tutorials, twerk videos have exploded in recognition over the last handful of years.|Mixtapes from DJ Godfather and artists like DJ Snake , Lil Jon , T-discomfort ,

  46. I appreciate, cause I found exactly what I was looking for. You have ended my four day long hunt! God Bless you man. Have a great day. Bye

  47. Very nice post and right to the point. I don’t know if this is truly the best place to ask but do you people have any thoughts on where to get some professional writers? Thanks in advance 🙂

  48. Enjoyed looking through this, very good stuff, appreciate it.

  49. Hey there would you mind sharing which blog platform you’re using? I’m going to start my own blog soon but I’m having a hard time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something completely unique. P.S Apologies for being off-topic but I had to ask!

  50. eklik.pl说道:

    Hello there! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done a outstanding job!

  51. You have mentioned very interesting details! ps decent internet site. “O human race born to fly upward, wherefore at a little wind dost thou fall.” by Dante Alighieri.

  52. Hello, Neat post. There is an issue with your site in internet explorer, could test this?K IE still is the market chief and a big section of other folks will omit your fantastic writing due to this problem.

  53. Blitzdiät说道:

    Hiya very nice blog!! Man .. Beautiful .. Superb .. I’ll bookmark your website and take the feeds alsoKI’m glad to seek out so many helpful information right here within the put up, we need develop more strategies on this regard, thanks for sharing. . . . . .

  54. Königskette说道:

    You have brought up a very excellent points, appreciate it for the post.

  55. I believe this is among the so much significant information for me. And i am satisfied studying your article. But wanna remark on some general issues, The site taste is great, the articles is actually nice : D. Excellent task, cheers

  56. Otoskop说道:

    It is really a great and helpful piece of info. I’m glad that you shared this useful information with us. Please keep us informed like this. Thank you for sharing.

  57. very nice put up, i actually love this web site, carry on it

  58. Werkzeugkoffer说道:

    My brother suggested I might like this web site. He used to be totally right. This publish truly made my day. You cann’t imagine just how a lot time I had spent for this information! Thanks!

  59. This web site is really a walk-through for all of the info you wanted about this and didn’t know who to ask. Glimpse here, and you’ll definitely discover it.

  60. Good info. Lucky me I reach on your website by accident, I bookmarked it.

  61. Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you can do with a few pics to drive the message home a little bit, but instead of that, this is magnificent blog. A great read. I will definitely be back.

  62. very good submit, i actually love this website, keep on it

  63. igracke za decu说道:

    I have been absent for some time, but now I remember why I used to love this site. Thank you, I?¦ll try and check back more frequently. How frequently you update your site?

  64. Nice read, I just passed this onto a friend who was doing some research on that. And he just bought me lunch because I found it for him smile So let me rephrase that: Thank you for lunch!

  65. seo说道:

    Hi there just wanted to give you a brief heads up and let you know a few of the pictures aren’t loading correctly. I’m not sure why but I think its a linking issue. I’ve tried it in two different web browsers and both show the same results.

  66. I do believe all the ideas you’ve presented for your post. They’re very convincing and will certainly work. Still, the posts are very quick for beginners. Could you please prolong them a little from next time? Thank you for the post.

  67. You have noted very interesting points! ps nice website .

  68. I was suggested this blog by my cousin. I’m not sure whether this post is written by him as no one else know such detailed about my trouble. You’re amazing! Thanks!

  69. organizations说道:

    It is in point of fact a great and helpful piece of info. I am happy that you just shared this helpful information with us. Please stay us informed like this. Thank you for sharing.

  70. We’re a group of volunteers and opening a new scheme in our community. Your website provided us with valuable information to work on. You have done an impressive job and our whole community will be grateful to you.

  71. Hi, I think your site might be having browser compatibility issues. When I look at your website in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, fantastic blog!

  72. PPsoccer说道:

    I like the valuable info you provide in your articles. I will bookmark your weblog and check again here frequently. I’m fairly certain I will be informed lots of new stuff proper here! Good luck for the next!

  73. Great line up. We will be linking to this great article on our site. Keep up the good writing.

  74. As I website possessor I think the content here is really fantastic, appreciate it for your efforts.

  75. Very nice post and straight to the point. I don’t know if this is really the best place to ask but do you folks have any ideea where to get some professional writers? Thanks 🙂

  76. Way cool, some valid points! I appreciate you making this article available, the rest of the site is also high quality. Have a fun.

  77. This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years. Great stuff, just great!

  78. I¦ve been exploring for a bit for any high-quality articles or blog posts in this sort of space . Exploring in Yahoo I ultimately stumbled upon this website. Studying this information So i am happy to show that I have a very excellent uncanny feeling I found out just what I needed. I so much indisputably will make sure to do not put out of your mind this web site and provides it a look on a constant basis.

  79. SEO services说道:

    You have brought up a very wonderful details , regards for the post.

  80. Hi! This post couldn’t be written any better! Reading this post reminds me of my good old room mate! He always kept chatting about this. I will forward this page to him. Fairly certain he will have a good read. Thanks for sharing!

  81. Very interesting information!Perfect just what I was searching for!

  82. Its superb as your other articles : D, appreciate it for putting up.

  83. Hello.This article was extremely remarkable, especially because I was looking for thoughts on this issue last Sunday.

  84. I like this website so much, bookmarked.

  85. Greetings! Quick question that’s entirely off topic. Do you know how to make your site mobile friendly? My web site looks weird when viewing from my iphone 4. I’m trying to find a template or plugin that might be able to correct this problem. If you have any recommendations, please share. Many thanks!

  86. secret detective说道:

    A large percentage of of whatever you mention is supprisingly appropriate and that makes me ponder why I hadn’t looked at this with this light previously. This piece truly did turn the light on for me as far as this issue goes. Nonetheless there is 1 position I am not too comfy with and while I try to reconcile that with the actual main idea of the issue, allow me observe exactly what all the rest of your readers have to point out.Nicely done.

  87. hello there and thank you to your info – I’ve definitely picked up anything new from proper here. I did then again expertise a few technical points the usage of this website, since I skilled to reload the website lots of times prior to I may just get it to load correctly. I had been considering in case your hosting is OK? Not that I am complaining, but slow loading instances occasions will often affect your placement in google and could damage your quality score if advertising and ***********|advertising|advertising|advertising and *********** with Adwords. Anyway I’m including this RSS to my e-mail and could glance out for a lot extra of your respective intriguing content. Ensure that you update this again soon..

  88. I just could not go away your website before suggesting that I extremely loved the usual info a person provide in your visitors? Is gonna be back ceaselessly in order to check up on new posts

  89. russellevatt说道:

    I went over this site and I believe you have a lot of great information, saved to fav (:.

  90. this site说道:

    2U6zoz Saw your material, and hope you publish more soon.

  91. Naked 100说道:

    It’s really a nice and useful piece of info. I am happy that you simply shared this helpful information with us. Please stay us up to date like this. Thanks for sharing.

  92. I?¦ve read a few excellent stuff here. Definitely value bookmarking for revisiting. I wonder how so much attempt you set to create any such fantastic informative web site.

  93. You could definitely see your expertise in the work you write. The world hopes for more passionate writers like you who aren’t afraid to say how they believe. Always go after your heart.

  94. you’re really a good webmaster. The web site loading speed is incredible. It seems that you’re doing any unique trick. In addition, The contents are masterwork. you have done a great job on this topic!

  95. Hettie Marolt说道:

    Will is inspired by it and introduces a new assignment: Twerk.|The girls are told they never have to take component but whoever wins will get a date with the boy of their choice.|She’s appeared in a number of music videos for artists like, Ludacris ,

  96. I like what you guys are up also. Such intelligent work and reporting! Keep up the excellent works guys I have incorporated you guys to my blogroll. I think it will improve the value of my site 🙂

  97. Autoquotes说道:

    Very nice article and straight to the point. I don’t know if this is in fact the best place to ask but do you guys have any ideea where to hire some professional writers? Thanks 🙂

  98. Youre so cool! I dont suppose Ive learn something like this before. So good to seek out someone with some original thoughts on this subject. realy thanks for starting this up. this website is one thing that is wanted on the internet, someone with somewhat originality. useful job for bringing one thing new to the internet!

  99. Mr jam and i started to become interested in the lifestyle
    about 3 years ago. Rachel Apt is enough any man capable of creating a
    character like Rachel Apt should live forever in literature.

  100. Lyman Alonzo说道:

    the rest is viral video history.|Gaunt’s dataset includes videos of girls alone in their bedrooms, or with buddies, or groups of girls in living rooms or backyards.|In my humble opinion these

  101. Guy might not want to share a room with me. It was getting
    dark and he quickly drove me home and let me out a couple of blocks from my house.

  102. Wow! This can be one particular of the most helpful blogs We have ever arrive across on this subject. Basically Excellent. I’m also a specialist in this topic therefore I can understand your hard work.

  103. I’d should check with you here. Which is not something I normally do! I take pleasure in studying a submit that will make people think. Additionally, thanks for permitting me to remark!

  104. I do trust all the concepts you have offered for your post. They’re very convincing and will definitely work. Still, the posts are very brief for starters. Could you please extend them a bit from subsequent time? Thank you for the post.

  105. I am glad to be one of the visitants on this outstanding website (:, thankyou for putting up.

  106. this site说道:

    EO1ywO I’а†ve learn several excellent stuff here. Certainly worth bookmarking for revisiting. I wonder how a lot attempt you set to make the sort of wonderful informative web site.

  107. After study a few of the blog posts on your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls check out my web site as well and let me know what you think.

  108. Brooks说道:

    magnificent issues altogether, you just gained a emblem new reader. What may you recommend about your publish that you made some days ago? Any certain?

  109. Doriserele说道:

    Revolutional update of SEO/SMM software “XRumer 16.0 + XEvil”:
    captchas regignizing of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another categories of captcha,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM software: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other programms.

    Interested? You can find a lot of demo videos about XEvil in YouTube.
    See you later 😉

    XRumer201707

  110. Nice read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch because I found it for him smile So let me rephrase that: Thanks for lunch! “A human being has a natural desire to have more of a good thing than he needs.” by Mark Twain.

  111. Nha ban TPHCM说道:

    I’m extremely impressed along with your writing abilities and also with the structure to your blog. Is this a paid subject matter or did you modify it your self? Anyway stay up the excellent quality writing, it is uncommon to see a nice weblog like this one these days..

  112. cats fails说道:

    I have been exploring for a little bit for any high quality articles or blog posts in this kind of space . Exploring in Yahoo I at last stumbled upon this web site. Studying this information So i am glad to show that I have a very just right uncanny feeling I found out just what I needed. I so much unquestionably will make certain to don’t overlook this website and give it a look regularly.

  113. Doriserele说道:

    Absolutely NEW update of SEO/SMM package “XRumer 16.0 + XEvil 3.0”:
    captchas regignizing of Google, Facebook, Bing, Hotmail, SolveMedia, Yandex,
    and more than 8400 another size-types of captchas,
    with highest precision (80..100%) and highest speed (100 img per second).
    You can connect XEvil 3.0 to all most popular SEO/SMM programms: XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other software.

    Interested? There are a lot of demo videos about XEvil in YouTube.
    Good luck 😉

    XRumer201707

发表评论

电子邮件地址不会被公开。