Re: [PERFORM] hardware advice
On 09/27/2012 10:22 PM, M. D. wrote: On 09/27/2012 02:55 PM, Scott Marlowe wrote: On Thu, Sep 27, 2012 at 2:46 PM, M. D. wrote: select item.item_id,item_plu.number,item.description, (select number from account where asset_acct = account_id), (select number from account where expense_acct = account_id), (select number from account where income_acct = account_id), (select dept.name from dept where dept.dept_id = item.dept_id) as dept, (select subdept.name from subdept where subdept.subdept_id = item.subdept_id) as subdept, (select sum(on_hand) from item_change where item_change.item_id = item.item_id) as on_hand, (select sum(on_order) from item_change where item_change.item_id = item.item_id) as on_order, (select sum(total_cost) from item_change where item_change.item_id = item.item_id) as total_cost from item join item_plu on item.item_id = item_plu.item_id and item_plu.seq_num = 0 where item.inactive_on is null and exists (select item_num.number from item_num where item_num.item_id = item.item_id) and exists (select stocked from item_store where stocked = 'Y' and inactive_on is null and item_store.item_id = item.item_id) Have you tried re-writing this query first? Is there a reason to have a bunch of subselects instead of joining the tables? What pg version are you running btw? A newer version of pg might help too. This query is inside an application (Quasar Accounting) written in Qt and I don't have access to the source code. Is there any prospect of the planner/executor being taught to merge each of those groups of three index scans, to aid this sort of poor query? -- Jeremy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] RE: [PERFORM] exponential performance decrease, problem with version postgres + RHEL?
John Nash wrote: > We have being doing some testing with an ISD transaction and we had > some problems that we posted here. > > The answers we got were very kind and useful but we couldn't solve the > problem. Could you refer to the threads so that you don't get the same advice again? > We have doing some investigations after this and we are thinking if is > it possible that OS has something to do with this issue. I mean, we > have two hosts, both of them with OS = Red Hat Enterprise Linux Server > release 6.2 (Santiago) > > But when doing "select * from version()" on the postgres shell we obtain: > > sessions=# select * from version(); >version > -- > > PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) > 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit > (1 row) > > We don't understand why in here it's written "(Red Hat 4.4.6-3)". > > Is it possible that we have installed a postgres' version that it's > not perfect for the OS? It means that the PostgreSQL you are using was compiled with a compiler that was compiled on RHEL4. Shouldn't be a problem. > But if this is a problem, why are we obtaining a normal perform on a > host and an exponential performance decrease on another? > > And how can we obtain a normal performance when launching the program > which does the queries from another host (remote url) but when > launching it in the same host we obtain this decrease on the > performance? Try to identify the bottleneck. Is it disk I/O, CPU, memory or something else? > name | > current_setting > > --+--- > - > -- > version | PostgreSQL 9.1.3 on > x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red > Hat > 4.4.6-3), 64-bit > archive_mode | off > client_encoding | UTF8 > fsync| on > lc_collate | en_US.UTF-8 > lc_ctype | en_US.UTF-8 > listen_addresses | * > log_directory| pg_log > log_filename | postgresql-%a.log > log_rotation_age | 1d > log_rotation_size| 0 > log_truncate_on_rotation | on > logging_collector| on > max_connections | 100 > max_stack_depth | 2MB > port | 50008 > server_encoding | UTF8 > shared_buffers | 32MB Now that sticks out as being pretty small. Try 1/4 of the memory available for the database, but not more than 2 GB. > synchronous_commit | on > TimeZone | Europe/Madrid > wal_buffers | 64kB That's also pretty small. > wal_sync_method | fsync > (22 rows) Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] "Select * " on 12-18M row table from remote machine thru JDBC - Performance nose-dives after 10M-ish records
On machine 1 - a table that contains between 12 and 18 million rows On machine 2 - a Java app that calls Select * on the table, and writes it into a Lucene index Originally had a fetchSize of 10,000 and would take around 38 minutes for 12 million, 50 minutes for 16ish million to read it all & write it all back out as the lucene index One day it started taking 4 hours. If something changed, we dont know what it was We tracked it down to, after 10 million or so rows, the Fetch to get the next 10,000 rows from the DB goes from like 1 second to 30 seconds, and stays there After spending a week of two devs & DBA trying to solve this, we eventually "solved" it by upping the FetchRowSize in the JDBC call to 50,000 It was performing well enough again for a few weeks then...one day... it started taking 4 hours again we tried upping the shared_buffer from 16GB to 20GB And last night... it took 7 hours we are using PGSQL 9.1 does anyone have ANY ideas?! thanks much -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-on-12-18M-row-table-from-remote-machine-thru-JDBC-Performance-nose-dives-after-10M-ish-records-tp5725853.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
Re: [PERFORM] "Select * " on 12-18M row table from remote machine thru JDBC - Performance nose-dives after 10M-ish records
I think the best advice I can think of is to go back to the basics. Tools like sar and top and look at logs. Changing random settings on both the client and server seems like guessing. I find it unlikely that the changes you made (jdbc and shared buffers) had the effects you noticed. Determine if it is I/O, CPU, or network. Put all your settings back to the way they were. If the DB did not change, then look at OS and network. Deron On Sep 28, 2012 6:53 AM, "antthelimey" wrote: > On machine 1 - a table that contains between 12 and 18 million rows > On machine 2 - a Java app that calls Select * on the table, and writes it > into a Lucene index > > Originally had a fetchSize of 10,000 and would take around 38 minutes for > 12 > million, 50 minutes for 16ish million to read it all & write it all back > out > as the lucene index > > One day it started taking 4 hours. If something changed, we dont know what > it was > > We tracked it down to, after 10 million or so rows, the Fetch to get the > next 10,000 rows from the DB goes from like 1 second to 30 seconds, and > stays there > > After spending a week of two devs & DBA trying to solve this, we > eventually > "solved" it by upping the FetchRowSize in the JDBC call to 50,000 > > It was performing well enough again for a few weeks > > then...one day... it started taking 4 hours again > > we tried upping the shared_buffer from 16GB to 20GB > > And last night... it took 7 hours > > we are using PGSQL 9.1 > > does anyone have ANY ideas?! > > thanks much > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Select-on-12-18M-row-table-from-remote-machine-thru-JDBC-Performance-nose-dives-after-10M-ish-records-tp5725853.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 >
Re: [PERFORM] hardware advice
On Thu, Sep 27, 2012 at 03:50:33PM -0500, Shaun Thomas wrote: > On 09/27/2012 03:44 PM, Scott Marlowe wrote: > > >This 100x this. We used to buy our boxes from aberdeeninc.com and got > >a 5 year replacement parts warranty included. We spent ~$10k on a > >server that was right around $18k from dell for the same numbers and a > >3 year warranty. > > Whatever you do, go for the Intel ethernet adaptor option. We've had > so many headaches with integrated broadcom NICs. :( > +++1 Sigh. Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] hardware advice
On 9/27/2012 1:56 PM, M. D. wrote: >> >> I'm in Belize, so what I'm considering is from ebay, where it's unlikely >> that I'll get the warranty. Should I consider some other brand rather? To >> build my own or buy custom might be an option too, but I would not get any >> warranty. Your best warranty would be to have the confidence to do your own repairs, and to have the parts on hand. I'd seriously consider putting your own system together. Maybe go to a few sites with pre-configured machines and see what parts they use. Order those, screw the thing together yourself, and put a spare of each critical part on your shelf. A warranty is useless if you can't use it in a timely fashion. And you could easily get better reliability by spending the money on spare parts. I'd bet that for the price of a warranty you can buy a spare motherboard, a few spare disks, a memory stick or two, a spare power supply, and maybe even a spare 3WARE RAID controller. Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] hardware advice
On 9/28/2012 9:46 AM, Craig James wrote: Your best warranty would be to have the confidence to do your own repairs, and to have the parts on hand. I'd seriously consider putting your own system together. Maybe go to a few sites with pre-configured machines and see what parts they use. Order those, screw the thing together yourself, and put a spare of each critical part on your shelf. This is what I did for years, but after taking my old parts collection to the landfill a few times, realized I may as well just buy N+1 machines and keep zero spares on the shelf. That way I get a spare machine available for use immediately, and I know the parts are working (parts on the shelf may be defective). If something breaks, I use the spare machine until the replacement parts arrive. Note in addition that a warranty can be extremely useful in certain organizations as a vehicle of blame avoidance (this may be its primary purpose in fact). If I buy a bunch of machines that turn out to have buggy NICs, well that's my fault and I can kick myself since I own the company, stay up late into the night reading kernel code, and buy new NICs. If I have an evil Dilbertian boss, then well...I'd be seriously thinking about buying Dell boxes in order to blame Dell rather than myself, and be able to say "everything is warrantied" if badness goes down. Just saying... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] hardware advice
On 09/28/2012 09:57 AM, David Boreham wrote: On 9/28/2012 9:46 AM, Craig James wrote: Your best warranty would be to have the confidence to do your own repairs, and to have the parts on hand. I'd seriously consider putting your own system together. Maybe go to a few sites with pre-configured machines and see what parts they use. Order those, screw the thing together yourself, and put a spare of each critical part on your shelf. This is what I did for years, but after taking my old parts collection to the landfill a few times, realized I may as well just buy N+1 machines and keep zero spares on the shelf. That way I get a spare machine available for use immediately, and I know the parts are working (parts on the shelf may be defective). If something breaks, I use the spare machine until the replacement parts arrive. Note in addition that a warranty can be extremely useful in certain organizations as a vehicle of blame avoidance (this may be its primary purpose in fact). If I buy a bunch of machines that turn out to have buggy NICs, well that's my fault and I can kick myself since I own the company, stay up late into the night reading kernel code, and buy new NICs. If I have an evil Dilbertian boss, then well...I'd be seriously thinking about buying Dell boxes in order to blame Dell rather than myself, and be able to say "everything is warrantied" if badness goes down. Just saying... I'm kinda in the latter shoes. Dell is the only thing that is trusted in my organisation. If I would build my own, I would be fully blamed for anything going wrong in the next 3 years. Thanks everyone for your input. Now my final choice will be if my budget allows for the latest and fastest, else I'm going for the x5690. I don't have hundreds of users, so I think the x5690 should do a pretty good job handling the load. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Possible Performance Regression with Transitive Comparisons vs. Constants
Hey guys, I ran into this while we were working on an upgrade project. We're moving from 8.2 (don't ask) to 9.1, and started getting terrible performance for some queries. I've managed to boil it down to a test case: create temp table my_foo as select a.id, '2012-01-01'::date + (random()*365)::int AS created_dt from generate_series(1,5000) as a(id); create temp table my_bar as select b.id, (random()*4999)::int + 1 as aid, '2012-01-01'::date + (random()*365)::int AS created_dt from generate_series(1,50) as b(id); analyze my_foo; analyze my_bar; create index idx_foo_id on my_foo (id); create index idx_foo_const on my_foo (created_dt); create index idx_bar_id on my_bar(id); create index idx_bar_aid on my_bar(aid); create index idx_bar_const on my_bar (created_dt); Ok, simple enough, right? Now do this: explain analyze select b.* from my_foo a, my_bar b where a.created_dt = '2012-05-05' and b.created_dt between a.created_dt and a.created_dt + interval '1 month'; explain analyze select b.* from my_foo a, my_bar b where a.created_dt = '2012-05-05' and b.created_dt between '2012-05-05' and '2012-05-05'::date + interval '1 month'; These do not create the same query plan, which itself is odd. But the other thing, is that query 1 is about 4-8x slower than query 2, but only when I test it on PostgreSQL 9.1. When I test it on 8.2 (eww) they're about equal in performance. I should note that the plan for both cases in 8.2, performs better than query 1 in 9.1. So I've got two questions: 1. Is it normal for trivially equal values to be non-optimal like this? 2. What on earth happened between 8.2 and 9.1 that made performance worse for this test case? Just to address any questions, I've tested this in multiple environments, and it's always consistent. 9.1 performs worse than 8.2 here, so long as you rely on PostgreSQL to make the equivalence instead of doing it manually. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Possible Performance Regression with Transitive Comparisons vs. Constants
Shaun Thomas writes: > I ran into this while we were working on an upgrade project. We're > moving from 8.2 (don't ask) to 9.1, and started getting terrible > performance for some queries. I've managed to boil it down to a test case: 9.1.what? For me, 8.2.23 and 9.1.6 produce the same plan and just about the same runtime for your query 1. For query 2, 9.1.6 prefers to stick in a Materialize node, which cuts the runtime 30% or so --- but if I set enable_material to off then I get the same plan and runtime as with 8.2. Perhaps you should show the EXPLAIN ANALYZE outputs you're actually getting, rather than assuming others will get the same thing. regards, tom lane (PS: it does seem that HEAD has got some kind of issue here, because it's picking a plain not bitmap indexscan. I'll go look at that. But I don't see that misbehavior in 9.1.) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Query plan, nested EXISTS
Howdy, I've been debugging a client's slow query today and I'm curious about the query plan. It's picking a plan that hashes lots of rows from the versions table (on v9.0.10)... EXPLAIN ANALYZE SELECT COUNT(*) FROM notes a WHERE a.project_id = 114 AND EXISTS ( SELECT 1 FROM note_links b WHERE b.note_id = a.id AND b.entity_type = 'Version' AND EXISTS ( SELECT 1 FROM versions c WHERE c.id = b.entity_id AND c.code ILIKE '%comp%' AND c.retirement_date IS NULL ) AND b.retirement_date IS NULL ) QUERY PLAN - Aggregate (cost=833177.30..833177.31 rows=1 width=0) (actual time=10806.416..10806.416 rows=1 loops=1) -> Hash Semi Join (cost=747004.15..833154.86 rows=8977 width=0) (actual time=10709.343..10806.344 rows=894 loops=1) Hash Cond: (a.id = b.note_id) -> Index Scan using notes_retirement_date_project on notes a (cost=0.00..66725.10 rows=12469 width=4) (actual time=12.213..71.199 rows=12469 loops=1) Index Cond: (project_id = 114) -> Hash (cost=723749.35..723749.35 rows=1417424 width=4) (actual time=10696.192..10696.192 rows=227261 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 2016kB -> Hash Semi Join (cost=620007.75..723749.35 rows=1417424 width=4) (actual time=8953.460..10645.714 rows=227261 loops=1) Hash Cond: (b.entity_id = c.id) -> Seq Scan on note_links b (cost=0.00..71849.56 rows=1417424 width=8) (actual time=0.075..628.183 rows=1509795 loops=1) Filter: ((retirement_date IS NULL) AND ((entity_type)::text = 'Version'::text)) -> Hash (cost=616863.62..616863.62 rows=251530 width=4) (actual time=8953.327..8953.327 rows=300115 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 10551kB -> Seq Scan on versions c (cost=0.00..616863.62 rows=251530 width=4) (actual time=176.590..8873.588 rows=300115 loops=1) Filter: ((retirement_date IS NULL) AND ((code)::text ~~* '%comp%'::text)) Total runtime: 10810.479 ms (16 rows) However, I can trick it into a better plan by adding LIMIT 1 into the inner EXISTS: EXPLAIN ANALYZE SELECT COUNT(*) FROM notes a WHERE a.project_id = 114 AND EXISTS ( SELECT 1 FROM note_links b WHERE b.note_id = a.id AND b.entity_type = 'Version' AND EXISTS ( SELECT 1 FROM versions c WHERE c.id = b.entity_id AND c.code ILIKE '%comp%' AND c.retirement_date IS NULL LIMIT 1 ) AND b.retirement_date IS NULL ) QUERY PLAN Aggregate (cost=372820.37..372820.38 rows=1 width=0) (actual time=139.430..139.430 rows=1 loops=1) -> Nested Loop Semi Join (cost=0.00..372809.15 rows=4488 width=0) (actual time=9.735..139.333 rows=894 loops=1) -> Index Scan using notes_retirement_date_project on notes a (cost=0.00..66725.10 rows=12469 width=4) (actual time=9.699..67.263 rows=12469 loops=1) Index Cond: (project_id = 114) -> Index Scan using note_links_note on note_links b (cost=0.00..24.54 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=12469) Index Cond: (b.note_id = a.id) Filter: ((b.retirement_date IS NULL) AND ((b.entity_type)::text = 'Version'::text) AND (SubPlan 1)) SubPlan 1 -> Limit (cost=0.00..9.04 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=11794) -> Index Scan using versions_pkey on versions c (cost=0.00..9.04 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=11794) Index Cond: (id = $0) Filter: ((retirement_date IS NULL) AND ((code)::text ~~* '%comp%'::text)) Total runtime: 139.465 ms (13 rows) Unfortunately, a couple other queries I tested got slower by adding the LIMIT so I don't think that's going to be a good workaround. It doesn't appear to be related to ILIKE, because I tried a straight equals against another un-indexed column of versions and still get a slow plan (and adding the LIMIT to this one made it fast too): EXPLAIN ANALYZE SELECT COUNT(*) FROM notes a WHERE a.project_id = 114 AND EXISTS ( SELECT 1 FROM note_links b WHERE b.note_id = a.id AND b.entity_type = 'Version' AND EXISTS ( SELECT 1 FROM versions c WHERE c.id = b.entity_id AND c.sg_status_list = 'ip' AND c.retirement_date IS NULL ) AND b.retirement_date IS NULL ) QUERY PLAN ---
Re: [PERFORM] Possible Performance Regression with Transitive Comparisons vs. Constants
On 09/28/2012 03:35 PM, Tom Lane wrote: 9.1.what? For me, 8.2.23 and 9.1.6 produce the same plan and just about the same runtime for your query 1. I withdraw that part of my question. I apparently didn't look closely enough at the actual output. I was basing the version assumption on the query speed on the new server, when it was probably due to cache effects. The first part of the question stands, though... Why isn't the optimizer substituting these values? a.created_date should be exactly equivalent to '2012-05-05', but it's clearly not being treated that way. With the full substitutions, I'm seeing things like this: http://explain.depesz.com/s/3T4 With the column names, it's this: http://explain.depesz.com/s/Fq7 This is on 8.2, but the behavior is the same on 9.1. From 130s to 23s simply by substituting the constant wherever the column name is encountered. For reference, the queries are, slow: select a.id, f.ezorder_id from reporting.account a join ezorder f on f.account_id = a.account_id where a.process_date = '2012-09-27' and f.date_created between a.process_date - interval '6 months' and a.process_date and a.row_out is null And fast: select a.id, f.ezorder_id from reporting.account a join ezorder f on f.account_id = a.account_id where a.process_date = '2012-09-27' and f.date_created between '2012-09-27'::date - interval '6 months' and '2012-09-27' and a.row_out is null We discovered this during the upgrade, but it seems to equally apply to both 8.2 and 9.1. I've been telling the devs to replace any of these they find all day. I can't quite say why we never "noticed" this before, but it got exposed today pretty plainly. If this were a compiler, I'd have expected it to treat the values as equivalent, but that's clearly not what's happening. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query plan, nested EXISTS
Matt Daw writes: > Howdy, I've been debugging a client's slow query today and I'm curious > about the query plan. It's picking a plan that hashes lots of rows from the > versions table (on v9.0.10)... > EXPLAIN ANALYZE > SELECT COUNT(*) FROM notes a WHERE > a.project_id = 114 AND > EXISTS ( > SELECT 1 FROM note_links b > WHERE > b.note_id = a.id AND > b.entity_type = 'Version' AND > EXISTS ( > SELECT 1 FROM versions c > WHERE > c.id = b.entity_id AND > c.code ILIKE '%comp%' AND > c.retirement_date IS NULL > ) AND > b.retirement_date IS NULL > ) I think the real problem here is that 9.0 is incapable of avoiding a full table scan on "note_links", which means it doesn't really have any better option than to do the inner EXISTS as a full-table semijoin. This is because it can't push a.id down through two levels of join, and because the semijoins don't commute, there's no way to get a.id into the scan of note_links to pull out only the useful rows. The hack with LIMIT avoids this problem by preventing the inner EXISTS from being treated as a full-fledged semijoin; but of course that hack leaves you vulnerable to very bad plans if the statistics are such that a nestloop join isn't the best bet for the inner EXISTS. The work I did for parameterized paths in 9.2 was intended to address exactly this type of scenario. I would be interested to know if 9.2 does this any better for you. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query plan, nested EXISTS
Hi Tom, thank you very much. I'll load these tables onto a 9.2 instance and report back. Matt On Fri, Sep 28, 2012 at 2:44 PM, Tom Lane wrote: > Matt Daw writes: > > Howdy, I've been debugging a client's slow query today and I'm curious > > about the query plan. It's picking a plan that hashes lots of rows from > the > > versions table (on v9.0.10)... > > > EXPLAIN ANALYZE > > SELECT COUNT(*) FROM notes a WHERE > > a.project_id = 114 AND > > EXISTS ( > > SELECT 1 FROM note_links b > > WHERE > > b.note_id = a.id AND > > b.entity_type = 'Version' AND > > EXISTS ( > > SELECT 1 FROM versions c > > WHERE > > c.id = b.entity_id AND > > c.code ILIKE '%comp%' AND > > c.retirement_date IS NULL > > ) AND > > b.retirement_date IS NULL > > ) > > I think the real problem here is that 9.0 is incapable of avoiding a > full table scan on "note_links", which means it doesn't really have any > better option than to do the inner EXISTS as a full-table semijoin. > This is because it can't push a.id down through two levels of join, and > because the semijoins don't commute, there's no way to get a.id into the > scan of note_links to pull out only the useful rows. The hack with > LIMIT avoids this problem by preventing the inner EXISTS from being > treated as a full-fledged semijoin; but of course that hack leaves you > vulnerable to very bad plans if the statistics are such that a nestloop > join isn't the best bet for the inner EXISTS. > > The work I did for parameterized paths in 9.2 was intended to address > exactly this type of scenario. I would be interested to know if 9.2 > does this any better for you. > > regards, tom lane >
Re: [PERFORM] Possible Performance Regression with Transitive Comparisons vs. Constants
Shaun Thomas writes: > The first part of the question stands, though... Why isn't the optimizer > substituting these values? a.created_date should be exactly equivalent > to '2012-05-05', but it's clearly not being treated that way. No version of Postgres has ever substituted constants in the way you're imagining, and I wouldn't hold my breath waiting for it to happen. The reason is that "x = constant" only creates a requirement for x to be btree-equal to the constant, and btree equality doesn't guarantee equality for all purposes. In this example we'd have to assume that btree-equality guaranteed identical results from the date + interval addition operator. While that happens to be true for this operator, the planner can't know that. A real-world example of the kind of case I'm worried about is that in IEEE-spec float arithmetic, minus zero and plus zero compare equal --- but there are functions that give different results for the two values. Another is that the char(n) type's equality operator will say that 'foo' and 'foo ' are equal, but those values are definitely distinguishable by some operations, eg length(). There are some cases where the planner can effectively propagate constants, but they rely on transitivity of btree equality operators. For instance if we have x = constant and x = y, with compatible equality operators, we can deduce y = constant. But that doesn't imply that y *is* the constant, just that it's btree-equal to it. There have been some discussions of inventing a stronger notion of equality than btree equality, so that we could know when it's safe to make this type of substitution; but nothing's been done about that. Personally I think it's fairly rare that any real win would come from this type of constant substitution, and so it's very likely that adding it would just create a net drag on performance (because of the added planner cycles spent looking for substitution opportunities, which would happen in every query whether it got any benefit or not). Another point here is that at least for the one side of your BETWEEN operator, b.created_dt >= a.created_dt, we could in fact combine that with a.created_dt = '2012-05-05' to deduce b.created_dt >= '2012-05-05', because we know from the btree opclass for dates that these = and >= operators have compatible semantics. Again though, it seems likely that the cost of looking for such opportunities would outweigh the benefits. In this particular example I don't think it'd do much good --- the reason the planner isn't picking a plan similar to the "fast" one is that it doesn't know that the BETWEEN with variable limits will select only a relatively small part of the table. Providing a constant limit for just one side wouldn't fix that. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query plan, nested EXISTS
Hi Tom, v9.2.1 looks good! Aggregate (cost=420808.99..420809.00 rows=1 width=0) (actual time=147.345..147.345 rows=1 loops=1) -> Nested Loop Semi Join (cost=0.00..420786.71 rows=8914 width=0) (actual time=13.847..147.219 rows=894 loops=1) -> Index Scan using notes_retirement_date_project on notes a (cost=0.00..67959.22 rows=12535 width=4) (actual time=13.811..71.741 rows=12469 loops=1) Index Cond: (project_id = 114) -> Nested Loop Semi Join (cost=0.00..28.14 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=12469) -> Index Scan using note_links_note on note_links b (cost=0.00..12.37 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=12469) Index Cond: (note_id = a.id) Filter: ((retirement_date IS NULL) AND ((entity_type)::text = 'Version'::text)) Rows Removed by Filter: 1 -> Index Scan using versions_pkey on versions c (cost=0.00..15.76 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=11794) Index Cond: (id = b.entity_id) Filter: ((retirement_date IS NULL) AND ((code)::text ~~* '%comp%'::text)) Rows Removed by Filter: 1 Total runtime: 147.411 ms (14 rows) On Fri, Sep 28, 2012 at 2:47 PM, Matt Daw wrote: > Hi Tom, thank you very much. I'll load these tables onto a 9.2 instance > and report back. > > Matt > > > On Fri, Sep 28, 2012 at 2:44 PM, Tom Lane wrote: > >> Matt Daw writes: >> > Howdy, I've been debugging a client's slow query today and I'm curious >> > about the query plan. It's picking a plan that hashes lots of rows from >> the >> > versions table (on v9.0.10)... >> >> > EXPLAIN ANALYZE >> > SELECT COUNT(*) FROM notes a WHERE >> > a.project_id = 114 AND >> > EXISTS ( >> > SELECT 1 FROM note_links b >> > WHERE >> > b.note_id = a.id AND >> > b.entity_type = 'Version' AND >> > EXISTS ( >> > SELECT 1 FROM versions c >> > WHERE >> > c.id = b.entity_id AND >> > c.code ILIKE '%comp%' AND >> > c.retirement_date IS NULL >> > ) AND >> > b.retirement_date IS NULL >> > ) >> >> I think the real problem here is that 9.0 is incapable of avoiding a >> full table scan on "note_links", which means it doesn't really have any >> better option than to do the inner EXISTS as a full-table semijoin. >> This is because it can't push a.id down through two levels of join, and >> because the semijoins don't commute, there's no way to get a.id into the >> scan of note_links to pull out only the useful rows. The hack with >> LIMIT avoids this problem by preventing the inner EXISTS from being >> treated as a full-fledged semijoin; but of course that hack leaves you >> vulnerable to very bad plans if the statistics are such that a nestloop >> join isn't the best bet for the inner EXISTS. >> >> The work I did for parameterized paths in 9.2 was intended to address >> exactly this type of scenario. I would be interested to know if 9.2 >> does this any better for you. >> >> regards, tom lane >> > >
[PERFORM] NestedLoops over BitmapScan question
Greetings.
I have a small monitoring query on the following tables:
select relname,relpages,reltuples::numeric(12) from pg_class where relname
in ('meta_version','account') order by 1;
relname| relpages | reltuples
--+--+---
account | 3235 |197723
meta_version | 710068 | 32561200
(2 rows)
The logical “body” of the query is:
select count(*) from meta_version where account_id in (select account_id
from account where customer_id = 8608064);
I know that due to the data distribution (above customer's accounts are
used in 45% of the meta_version table) I
cannot expect fast results. But I have another question.
With default default_statistics_target I get the following plan:
http://explain.depesz.com/s/jri
In order to get better estimates, I've increased statistics targets to 200
for account.customer_id and meta_version.account_id.
Now I have the following plan:
http://explain.depesz.com/s/YZJ
Second query takes twice more time.
My questions are:
- why with better statistics planner chooses to do a SeqScan in favor of
BitmapIndexScan inside the NestedLoops?
- is it possible to adjust this decision by changing other GUCs, perhaps
costs?
- would it be correct to adjust seq_page_cost and random_page_cost based on
the IOPS of the underlying disks?
any other metrics should be considered?
I'm running on a:
name|
current_setting
+---
version| PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu,
compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
archive_command| test ! -f $PG_WAL/%f && cp %p $PG_WAL/%f
archive_mode | on
bgwriter_delay | 50ms
bgwriter_lru_maxpages | 200
checkpoint_segments| 25
checkpoint_timeout | 30min
client_encoding| UTF8
effective_cache_size | 8GB
hot_standby| on
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_checkpoints| on
log_connections| on
log_destination| csvlog
log_directory | ../../log/CLUSTER
log_disconnections | on
log_file_mode | 0640
log_filename | pg-%Y%m%d_%H%M%S.log
log_line_prefix| %u:%d:%a:%h:%c:%x:%t>
log_lock_waits | on
log_min_duration_statement | 300ms
log_rotation_age | 1d
log_rotation_size | 0
log_temp_files | 20MB
logging_collector | on
maintenance_work_mem | 512MB
max_connections| 200
max_prepared_transactions | 0
max_stack_depth| 2MB
max_wal_senders| 2
port | 9120
server_encoding| UTF8
shared_buffers | 5GB
silent_mode| on
ssl| on
ssl_renegotiation_limit| 0
tcp_keepalives_idle| 0
temp_buffers | 256MB
TimeZone | US/Eastern
wal_buffers| 512kB
wal_keep_segments | 0
wal_level | hot_standby
wal_sender_delay | 1s
work_mem | 32MB
Regards.
--
Victor Y. Yegorov
Re: [PERFORM] hardware advice
On Fri, Sep 28, 2012 at 11:33 AM, M. D. wrote: > On 09/28/2012 09:57 AM, David Boreham wrote: >> >> On 9/28/2012 9:46 AM, Craig James wrote: >>> >>> Your best warranty would be to have the confidence to do your own >>> repairs, and to have the parts on hand. I'd seriously consider >>> putting your own system together. Maybe go to a few sites with >>> pre-configured machines and see what parts they use. Order those, >>> screw the thing together yourself, and put a spare of each critical >>> part on your shelf. >>> >> This is what I did for years, but after taking my old parts collection to >> the landfill a few times, realized I may as well just buy N+1 machines and >> keep zero spares on the shelf. That way I get a spare machine available for >> use immediately, and I know the parts are working (parts on the shelf may be >> defective). If something breaks, I use the spare machine until the >> replacement parts arrive. >> >> Note in addition that a warranty can be extremely useful in certain >> organizations as a vehicle of blame avoidance (this may be its primary >> purpose in fact). If I buy a bunch of machines that turn out to have buggy >> NICs, well that's my fault and I can kick myself since I own the company, >> stay up late into the night reading kernel code, and buy new NICs. If I have >> an evil Dilbertian boss, then well...I'd be seriously thinking about buying >> Dell boxes in order to blame Dell rather than myself, and be able to say >> "everything is warrantied" if badness goes down. Just saying... >> > I'm kinda in the latter shoes. Dell is the only thing that is trusted in my > organisation. If I would build my own, I would be fully blamed for anything > going wrong in the next 3 years. Thanks everyone for your input. Now my > final choice will be if my budget allows for the latest and fastest, else > I'm going for the x5690. I don't have hundreds of users, so I think the > x5690 should do a pretty good job handling the load. If people in your organization trust Dell, they just haven't dealt with them enough. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
Kiriakos Tsourapas, 25.09.2012 13:01: Thank you, I will take this into consideration, since upgrading to 9 will be much harder I assume... I think an upgrade from 8.3 to 8.4 was "harder" due to the removal of a lot of implicit type casts. 8.4 to 9.x shouldn't be that problematic after all (but will take longer due to the required dump/reload) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] wrong join result set estimate
Hello, i have a problem with relatively easy query. EXPLAIN ANALYZE SELECT content.* FROM content JOIN blog ON blog.id = content.blog_id JOIN community_prop ON blog.id = community_prop.blog_id JOIN community ON community.id = community_prop.id WHERE community.id IN (33, 55, 61, 1741, 75, 90, 106, 180, 228, 232, 256, 310, 388, 404, 504, 534, 536, 666, 700, 768, 824, 832, 855, 873, 898, 962, 1003, 1008, 1027, 1051, 1201, 1258, 1269, 1339, 1355, 1360, 1383, 1390, 1430, 1505, 1506, 1530, 1566, 1578, 1616, 1678, 1701, 1713, 1723, 1821, 1842, 1880, 1882, 1894, 1973, 2039, 2069, 2106, 2130, 2204, 2226, 2236, 2238, 2263, 2272, 2310, 2317, 2327, 2353, 2360, 2401, 2402, 2409, 2419, 2425, 2426, 2438, 2440, 2452, 2467, 2494, 2514, 2559, 2581, 2653, 2677, 2679, 2683, 2686, 2694, 2729, 2732, 2739, 2779, 2785, 2795, 2821, 2831, 2839, 2862, 2864, 2866, 2882, 2890, 2905, 2947, 2962, 2964, 2978, 2981, 3006, 3016, 3037, 3039, 3055, 3060, 3076, 3112, 3124, 3135, 3138, 3186, 3213, 3222, 3225, 3269, 3273, 3288, 3291, 3329, 3363, 3375, 3376, 3397, 3415, 3491, 3500, 2296, 3547, 129, 1039, 8, 1053, 1441, 2372, 1974, 289, 2449, 2747, 2075, 57, 3550, 3069, 89, 1603, 1570, 54, 152, 1035, 1456, 506, 1387, 43, 1805, 1851, 1843, 2587, 1908, 1790, 2630, 901, 13, 529, 705, 81, 2668, 1086, 603, 1986, 2516, 2969, 2671, 568, 4636, 1115, 864, 381, 4516, 2608, 677, 88, 1825, 3220, 3284, 947, 1190, 2233, 4489, 3320, 2957, 4146, 1841, 25, 643, 4352, 14, 4261, 3876, 1311, 1342, 4057, 3974) ORDER BY content.time_create DESC LIMIT 10; http://explain.depesz.com/s/ccE As you can see, planner estimates 115 rows, but there are 259554 of them. This query shows root of the problem EXPLAIN ANALYZE SELECT content.* FROM content JOIN blog ON blog.id = content.blog_id JOIN community_prop ON blog.id = community_prop.blog_id; QUERY PLAN - Hash Join (cost=24498.17..137922.26 rows=2624 width=572) (actual time=36.028..1342.267 rows=408374 loops=1) Hash Cond: (content.blog_id = blog.id) -> Seq Scan on content (cost=0.00..102364.99 rows=1260899 width=572) (actual time=0.030..983.274 rows=1256128 loops=1) -> Hash (cost=24439.07..24439.07 rows=4728 width=8) (actual time=35.964..35.964 rows=4728 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 185kB -> Nested Loop (cost=0.00..24439.07 rows=4728 width=8) (actual time=0.064..33.092 rows=4728 loops=1) -> Seq Scan on community_prop (cost=0.00..463.28 rows=4728 width=4) (actual time=0.004..5.089 rows=4728 loops=1) -> Index Scan using blog_pkey on blog (cost=0.00..5.06 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=4728) Index Cond: (id = community_prop.blog_id) Total runtime: 1361.354 ms 2624 vs 408374 Joining only content with blog: 1260211 vs 1256124. Joining only blog with community_prop: 4728 vs 4728 Joining only content with community_prop: 78304 vs 408376 SHOW default_statistics_target ; default_statistics_target --- 500 I already altered stats on blog_id column ALTER TABLE content ALTER COLUMN blog_id SET STATISTICS 1000; Tried setting 3000 and 1 on all join columns - did not make a difference. Tried setting n_distinct on content(blog_id) manually to different values from 1 to 20 (exact distinct is 90k, vacuum sets it to 76k) - did not change the estimate result set, only estimated index lookup. Don't now what to do with this. Ready to provide any additional information. Thank you for your time.
Re: [GENERAL] [PERFORM] Inaccurate Explain Cost
Am 27.09.2012, 02:04 Uhr, schrieb Jeff Janes : On Wed, Sep 26, 2012 at 1:21 PM, hubert depesz lubaczewski wrote: On Wed, Sep 26, 2012 at 02:38:09PM -0400, Robert Sosinski wrote: The first query shows a cost of 190,169.55 and runs in 199,806.951 ms. When I disable nested loop, I get a cost of 2,535,992.34 which runs in only 133,447.790 ms. We have run queries on our database with a cost of 200K cost before and they ran less then a few seconds, which makes me wonder if the first query plan is inaccurate. The other issue is understanding why a query plan with a much higher cost is taking less time to run. Are you under impression that cost should be somehow related to actual time? I am certainly under that impression. If the estimated cost has nothing to do with run time, then what is it that the cost-based optimizer is trying to optimize? See http://www.postgresql.org/docs/9.2/static/runtime-config-query.html section "18.7.2. Planner Cost Constants". -Matthias -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
