Re: [PERFORM] Different query plans on same servers
On 12/06/2011 09:00 PM, Tom Lane wrote: Mario Splivalo mario.spliv...@megafon.hr writes: I have 8.4.8 on producion and 8.4.9 on test, could that explain the difference in plans chosen? I'd wonder first if you have the same statistics settings on both. The big problem here is that the estimation of the join size is bad (8588 versus 0). Just an update here. I did downgrade postgres on testbox to 8.4.8 and now it's choosing bad plan there too. So we upgraded postgres on production server and the bad plan went away. We're preparing for upgrade to 9.1 now, we hope to offload some of the SELECTs to the slave server, we'll see how that will work. Thank you for your inputs! Mario -- 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] Response time increases over time
On 12/07/2011 09:23 AM, Havasvölgyi Ottó wrote: Thanks, Josh. The only reason I tried 8.4 first is that it was available for Debian as compiled package, so it was simpler for me to do it. Anyway I am going to test 9.1 too. I will post about the results. If you're using squeeze, you can get 9.1 from the debian backports. Mario -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Different query plans on same servers
I have a fairly simple query: SELECT some columns FROM tubesite_image INNER JOIN tubesite_object ON (tubesite_image.object_ptr_id = tubesite_object.id) WHERE tubesite_object.site_id = 8 ORDER BY tubesite_object.pub_date ASC LIMIT 21; That query is having a bad query plan on production server: Limit (cost=0.00..1938.67 rows=21 width=275) (actual time=3270.000..3270.000 rows=0 loops=1) - Nested Loop (cost=0.00..792824.51 rows=8588 width=275) (actual time=3269.997..3269.997 rows=0 loops=1) - Index Scan using tubesite_object_pub_date_idx on tubesite_object (cost=0.00..789495.13 rows=9711 width=271) (actual time=0.011..3243.629 rows=9905 loops=1) Filter: (site_id = 8) - Index Scan using tubesite_image_pkey on tubesite_image (cost=0.00..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=9905) Index Cond: (tubesite_image.object_ptr_id = tubesite_object.id) Total runtime: 3270.071 ms But, when I turn off nested loops, the query flies: QUERY PLAN -- Limit (cost=31384.35..31384.40 rows=21 width=275) (actual time=37.988..37.988 rows=0 loops=1) - Sort (cost=31384.35..31405.82 rows=8588 width=275) (actual time=37.986..37.986 rows=0 loops=1) Sort Key: tubesite_object.pub_date Sort Method: quicksort Memory: 25kB - Hash Join (cost=857.00..31152.80 rows=8588 width=275) (actual time=37.968..37.968 rows=0 loops=1) Hash Cond: (tubesite_object.id = tubesite_image.object_ptr_id) - Bitmap Heap Scan on tubesite_object (cost=596.77..30685.30 rows=9711 width=271) (actual time=7.414..25.132 rows=9905 loops=1) Recheck Cond: (site_id = 8) - Bitmap Index Scan on tubesite_object_site_id (cost=0.00..594.34 rows=9711 width=0) (actual time=4.943..4.943 rows=9905 loops=1) Index Cond: (site_id = 8) - Hash (cost=152.88..152.88 rows=8588 width=4) (actual time=4.620..4.620 rows=8588 loops=1) - Seq Scan on tubesite_image (cost=0.00..152.88 rows=8588 width=4) (actual time=0.005..2.082 rows=8588 loops=1) Total runtime: 38.071 ms I have rsynced the database from the prod server to the test server, that has same configuration (shared buffers, work mem, estimated cache size, and so on), and there it chooses bitmap heap scan with hash join without disabling the nested loops. I have 8.4.8 on producion and 8.4.9 on test, could that explain the difference in plans chosen? -- 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] Different query plans on same servers
On 12/06/2011 09:00 PM, Tom Lane wrote: Mario Splivalo mario.spliv...@megafon.hr writes: I have 8.4.8 on producion and 8.4.9 on test, could that explain the difference in plans chosen? I'd wonder first if you have the same statistics settings on both. The big problem here is that the estimation of the join size is bad (8588 versus 0). They do, I guess. I did rsync postgres datadir from the prod server to the test server. The only difference is that prod server was a bit more loaded than the test server. Mario -- 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] Different query plans on same servers
On 12/06/2011 09:17 PM, Kevin Grittner wrote: The hash join path must look more expensive on the first machine, for some reason. Mario, could you post the result of running this query from both servers?: http://wiki.postgresql.org/wiki/Server_Configuration Sure. Here is from the prod server: name | current_setting -+ version | PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit checkpoint_segments | 64 default_statistics_target | 2000 effective_cache_size| 36GB external_pid_file | /var/run/postgresql/8.4-main.pid lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 listen_addresses| * log_autovacuum_min_duration | 0 log_checkpoints | on log_line_prefix | %t [%p]: [%l-1] [%d] log_min_duration_statement | 1s maintenance_work_mem| 256MB max_connections | 1500 max_stack_depth | 3MB port| 5432 server_encoding | UTF8 shared_buffers | 4GB statement_timeout | 30min temp_buffers| 4096 TimeZone| localtime track_activity_query_size | 2048 unix_socket_directory | /var/run/postgresql wal_buffers | 128MB work_mem| 64MB And here is from the test server: name| current_setting +-- version| PostgreSQL 8.4.9 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit checkpoint_segments| 64 default_statistics_target | 2000 effective_cache_size | 36GB external_pid_file | /var/run/postgresql/8.4-main.pid lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 listen_addresses | * log_connections| on log_disconnections | on log_line_prefix| %t [%p]: [%l-1] [%d] log_min_duration_statement | 0 maintenance_work_mem | 256MB max_connections| 40 max_stack_depth| 3MB port | 5432 server_encoding| UTF8 shared_buffers | 4GB ssl| on temp_buffers | 4096 TimeZone | localtime unix_socket_directory | /var/run/postgresql wal_buffers| 128MB work_mem | 64MB (24 rows) At the time of doing 'explain analyze' on the prod server there were cca 80 connections on the server. Mario -- 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] Different query plans on same servers
On 12/06/2011 09:29 PM, Kevin Grittner wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: But both servers develop that estimate for the join size. [sigh] Those *were* both from the production server. Please show us the EXPLAIN ANALYZE from the other server. Huh, right... missed that one. Here is the 'explain analyze' from the other server: QUERY PLAN -- Limit (cost=31531.75..31531.80 rows=21 width=275) (actual time=45.584..45.584 rows=0 loops=1) - Sort (cost=31531.75..31531.84 rows=36 width=275) (actual time=45.579..45.579 rows=0 loops=1) Sort Key: tubesite_object.pub_date Sort Method: quicksort Memory: 25kB - Hash Join (cost=866.34..31530.82 rows=36 width=275) (actual time=45.544..45.544 rows=0 loops=1) Hash Cond: (tubesite_object.id = tubesite_image.object_ptr_id) - Bitmap Heap Scan on tubesite_object (cost=606.11..31146.68 rows=9884 width=271) (actual time=6.861..37.497 rows=9905 loops=1) Recheck Cond: (site_id = 8) - Bitmap Index Scan on tubesite_object_site_id (cost=0.00..603.64 rows=9884 width=0) (actual time=4.792..4.792 rows=9905 loops=1) Index Cond: (site_id = 8) - Hash (cost=152.88..152.88 rows=8588 width=4) (actual time=3.816..3.816 rows=8588 loops=1) - Seq Scan on tubesite_image (cost=0.00..152.88 rows=8588 width=4) (actual time=0.003..1.740 rows=8588 loops=1) Total runtime: 45.798 ms This is also a query from the prod server, but without LIMIT: Sort (cost=31713.95..31735.42 rows=8588 width=275) (actual time=60.311..60.311 rows=0 loops=1) Sort Key: tubesite_object.pub_date Sort Method: quicksort Memory: 25kB - Hash Join (cost=857.00..31152.80 rows=8588 width=275) (actual time=60.255..60.255 rows=0 loops=1) Hash Cond: (tubesite_object.id = tubesite_image.object_ptr_id) - Bitmap Heap Scan on tubesite_object (cost=596.77..30685.30 rows=9711 width=271) (actual time=8.682..49.721 rows=9905 loops=1) Recheck Cond: (site_id = 8) - Bitmap Index Scan on tubesite_object_site_id (cost=0.00..594.34 rows=9711 width=0) (actual time=5.705..5.705 rows=9905 loops=1) Index Cond: (site_id = 8) - Hash (cost=152.88..152.88 rows=8588 width=4) (actual time=4.281..4.281 rows=8588 loops=1) - Seq Scan on tubesite_image (cost=0.00..152.88 rows=8588 width=4) (actual time=0.005..1.437 rows=8588 loops=1) Total runtime: 60.483 ms (12 rows) I will try to rsync prod database to 8.4.8 on test server tomorrow, and see what happens. Hopefully upgrade to 8.4.9 (or even 8.4.10 if Debian packages is by tomorrow) will solve the issue... Mario -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Planner choosing NestedLoop, although it is slower...
Hi, all. I have a query, looking like this: SELECT pub_date FROM tubesite_object INNER JOIN tubesite_image ON tubesite_image.object_ptr_id = tubesite_object.id WHERE tubesite_object.site_id = 8 AND tubesite_object.pub_date E'2011-07-12 13:25:00' ORDER BY tubesite_object.pub_date ASC LIMIT 21; That query takes cca 10-15 seconds to run. Here is query plan: Limit (cost=0.00..415.91 rows=21 width=8) (actual time=11263.089..11263.089 rows=0 loops=1) - Nested Loop (cost=0.00..186249.55 rows=9404 width=8) (actual time=11263.087..11263.087 rows=0 loops=1) - Index Scan using tubesite_object_pub_date_idx on tubesite_object (cost=0.00..183007.09 rows=9404 width=12) (actual time=0.024..11059.487 rows=9374 loops=1) Index Cond: (pub_date '2011-07-12 13:25:00-05'::timestamp with time zone) Filter: (site_id = 8) - Index Scan using tubesite_image_pkey on tubesite_image (cost=0.00..0.33 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=9374) Index Cond: (tubesite_image.object_ptr_id = tubesite_object.id) Total runtime: 11263.141 ms This query runs quickly (around second or two) when there is only few connections to the database. Once I have 50-80 connections (200 is the limit, although I never have more than 120-150 connections), that query takes around 10-15 seconds. But, if I disable nestedloops, here is the query plan: Limit (cost=22683.45..22683.51 rows=21 width=8) (actual time=136.009..136.009 rows=0 loops=1) - Sort (cost=22683.45..22706.96 rows=9404 width=8) (actual time=136.007..136.007 rows=0 loops=1) Sort Key: tubesite_object.pub_date Sort Method: quicksort Memory: 25kB - Hash Join (cost=946.51..22429.91 rows=9404 width=8) (actual time=135.934..135.934 rows=0 loops=1) Hash Cond: (tubesite_object.id = tubesite_image.object_ptr_id) - Bitmap Heap Scan on tubesite_object (cost=545.40..21828.97 rows=9404 width=12) (actual time=20.874..104.075 rows=9374 loops=1) Recheck Cond: (site_id = 8) Filter: (pub_date '2011-07-12 13:25:00-05'::timestamp with time zone) - Bitmap Index Scan on tubesite_object_site_id (cost=0.00..543.05 rows=9404 width=0) (actual time=18.789..18.789 rows=9374 loops=1) Index Cond: (site_id = 8) - Hash (cost=215.49..215.49 rows=14849 width=4) (actual time=21.068..21.068 rows=14849 loops=1) - Seq Scan on tubesite_image (cost=0.00..215.49 rows=14849 width=4) (actual time=0.029..9.073 rows=14849 loops=1) Total runtime: 136.287 ms Now, if I disable nested loops in postgres.conf, then my load average on the server goes skyhigh (i presume because a lot of other queries are now being planned incorrectly). I have set up default_statistics_target to 2000, and have vacumed and analyzed the database. Here are the other options I have set up in postgresql.conf (that differ from the default settings): version | PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit checkpoint_segments | 64 default_statistics_target | 2000 effective_cache_size| 20GB external_pid_file | /var/run/postgresql/8.4-main.pid lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 listen_addresses| * log_autovacuum_min_duration | 0 log_checkpoints | on log_line_prefix | %t [%p]: [%l-1] log_min_duration_statement | 1s maintenance_work_mem| 256MB max_connections | 200 max_stack_depth | 3MB port| 5432 server_encoding | UTF8 shared_buffers | 2GB statement_timeout | 30min temp_buffers| 4096 TimeZone| localtime track_activity_query_size | 2048 unix_socket_directory | /var/run/postgresql wal_buffers | 128MB work_mem| 64MB Why is planner using NestedLoops, that is, what can I do to make him NOT to use NestedLoops (other than issuing SET enable_nestloop TO false; before each query) ? Mario -- 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] Planner choosing NestedLoop, although it is slower...
On 07/12/2011 10:04 PM, Tom Lane wrote: Mario Splivalomario.spliv...@megafon.hr writes: Limit (cost=0.00..415.91 rows=21 width=8) (actual time=11263.089..11263.089 rows=0 loops=1) - Nested Loop (cost=0.00..186249.55 rows=9404 width=8) (actual time=11263.087..11263.087 rows=0 loops=1) Why is planner using NestedLoops, Because it thinks the LIMIT will kick in and end the query when the join is only 21/9404ths (ie, a fraction of a percent) complete. A NestLoop results in saving a lot of work in that situation, whereas hash-and-sort has to do the whole join despite the LIMIT. What you need to look into is why the estimated join size is 9400 rows when the actual join size is zero. Are both tables ANALYZEd? Are you intentionally selecting rows that have no join partners? Hi, Tom. Yes, both tables have been ANALYZEd. What do you mean, intentilnaly selecting rows taht have no join partners? Mario -- 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] Planner choosing NestedLoop, although it is slower...
On 07/13/2011 12:39 AM, Tom Lane wrote: Mario Splivalomario.spliv...@megafon.hr writes: On 07/12/2011 10:04 PM, Tom Lane wrote: What you need to look into is why the estimated join size is 9400 rows when the actual join size is zero. Are both tables ANALYZEd? Are you intentionally selecting rows that have no join partners? Yes, both tables have been ANALYZEd. What do you mean, intentilnaly selecting rows taht have no join partners? I'm wondering why the actual join size is zero. That seems like a rather unexpected case for a query like this. It is true that this particular query returns 0 rows. But it's created by django, and I can't do much to alter it. Mario -- 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] Planner choosing NestedLoop, although it is slower...
On 07/13/2011 12:39 AM, Tom Lane wrote: Mario Splivalomario.spliv...@megafon.hr writes: On 07/12/2011 10:04 PM, Tom Lane wrote: What you need to look into is why the estimated join size is 9400 rows when the actual join size is zero. Are both tables ANALYZEd? Are you intentionally selecting rows that have no join partners? Yes, both tables have been ANALYZEd. What do you mean, intentilnaly selecting rows taht have no join partners? I'm wondering why the actual join size is zero. That seems like a rather unexpected case for a query like this. Yes, seems that planer gets confused by LIMIT. This query: select * from tubesite_object join tubesite_image on id=object_ptr_id where site_id = 8 and pub_date '2011-07-12 13:25:00' order by pub_date desc ; Does not choose Nested Loop, and is done instantly (20 ms), and returns no rows. However, if I add LIMIT at the end, it chooses NestedLoop and it takes 500ms if I'm alone on the server, and 10+ seconds if there 50+ connections on the server. Mario -- 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] Planner choosing NestedLoop, although it is slower...
On 07/13/2011 02:53 AM, Mario Splivalo wrote: On 07/13/2011 12:39 AM, Tom Lane wrote: Mario Splivalomario.spliv...@megafon.hr writes: On 07/12/2011 10:04 PM, Tom Lane wrote: What you need to look into is why the estimated join size is 9400 rows when the actual join size is zero. Are both tables ANALYZEd? Are you intentionally selecting rows that have no join partners? Yes, both tables have been ANALYZEd. What do you mean, intentilnaly selecting rows taht have no join partners? I'm wondering why the actual join size is zero. That seems like a rather unexpected case for a query like this. Yes, seems that planer gets confused by LIMIT. This query: select * from tubesite_object join tubesite_image on id=object_ptr_id where site_id = 8 and pub_date '2011-07-12 13:25:00' order by pub_date desc ; Does not choose Nested Loop, and is done instantly (20 ms), and returns no rows. However, if I add LIMIT at the end, it chooses NestedLoop and it takes 500ms if I'm alone on the server, and 10+ seconds if there 50+ connections on the server. As explained/suggested by RhodiumToad on IRC, adding composite index on (site_id, pub_date) made nestedloop query finish in around 100 seconds! Thank you! Mario -- 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 INTO large FKyed table is slow
On 12/01/2010 10:43 PM, Pierre C wrote: On Wed, 01 Dec 2010 18:24:35 +0100, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Mladen Gogala mladen.gog...@vmsinfo.com wrote: There is a operating system which comes with a very decent extent based file system and a defragmentation tool, included in the OS. The file system is called NTFS Been there, done that. Not only was performance quite poor compared to Linux, but reliability and staff time to manage things suffered in comparison to Linux. Please don't start with NTFS. It is the worst excuse for a filesystem I've ever seen. It is OT, but, could you please shead just some light on that? Part of my next project is to test performance of pg9 on both windows and linux systems so I'd appreciate any data/info you both may have. Mario -- 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 INTO large FKyed table is slow
On 12/01/2010 01:51 AM, Pierre C wrote: Now I tried removing the constraints from the history table (including the PK) and the inserts were fast. After few 'rounds' of inserts I added constraints back, and several round after that were fast again. But then all the same. Insert of some 11k rows took 4 seconds (with all constraints) and now the last one of only 4k rows took one minute. I did vacuum after each insert. Mario Hm, so for each line of drones_history you insert, you also update the correspoding drones table to reflect the latest data, right ? Yes. How many times is the same row in drones updated ? ie, if you insert N rows in drones_nistory, how may drone_id's do you have ? Just once. If I have 5000 lines in CSV file (that I load into 'temporary' table using COPY) i can be sure that drone_id there is PK. That is because CSV file contains measurements from all the drones, one measurement per drone. I usualy have around 100 new drones, so I insert those to drones and to drones_history. Then I first insert into drones_history and then update those rows in drones. Should I try doing the other way around? Although, I think I'm having some disk-related problems because when inserting to the tables my IO troughput is pretty low. For instance, when I drop constraints and then recreate them that takes around 15-30 seconds (on a 25M rows table) - disk io is steady, around 60 MB/s in read and write. It just could be that the ext3 partition is so fragmented. I'll try later this week on a new set of disks and ext4 filesystem to see how it goes. Mario -- 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 INTO large FKyed table is slow
On 12/01/2010 02:47 AM, Joshua D. Drake wrote: On Sun, 2010-11-28 at 12:46 +0100, Mario Splivalo wrote: The database for monitoring certain drone statuses is quite simple: This is the slow part: INSERT INTO drones_history (sample_id, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM tmpUpdate; For 100 rows this takes around 2 seconds. For 1000 rows this takes around 40 seconds. For 5000 rows this takes around 5 minutes. For 50k rows this takes around 30 minutes! Now this is where I start lag because I get new CSV every 10 minutes or so. Have you considered making the foreign key check deferrable? Yes, as Mladen Gogala had advised. No noticable change in performance - it's still slow :) But, just for the sake of clarification - I tought that DEFERRABLE would matter if I do a lot of INSERTs, inside a FOR loop or something like that. Since I'm doing INSERT INTO ... SELECT, does it makes any difference? Mario -- 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 INTO large FKyed table is slow
On 12/01/2010 05:34 PM, Mladen Gogala wrote: Mario Splivalo wrote: Yes, as Mladen Gogala had advised. No noticable change in performance - it's still slow :) Declaring constraints as deferrable doesn't do anything as such, you have to actually set the constraints deferred to have an effect. You have to do it within a transaction block. If done outside of the transaction block, there is no effect: I understand, I did as you suggested. Begin; Set constraints all deferred; select my_insert_drones_function(); commit I was able to insert the same value twice, it only failed at the end of the transaction. But, just for the sake of clarification - I tought that DEFERRABLE would matter if I do a lot of INSERTs, inside a FOR loop or something like that. Since I'm doing INSERT INTO ... SELECT, does it makes any difference? You cannot tell which part takes a long time, select or insert, without profiling. I certainly cannot do it over the internet. If I first select to a dummy temprary table, that SELECT is fast. Just INSERT INTO SELECT is slow. I'll try what Pierre suggested, on whole new filesystem. This one did get quite filled with thousands of files that I deleted while the database was working. Mario -- 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] Simple database, multiple instances?
On 11/30/2010 12:45 PM, Dimitri Fontaine wrote: Mario Splivalomario.spliv...@megafon.hr writes: I have simple database schema, containing just three tables: samples, drones, drones_history. Now, those tables hold data for the drones for a simulation. Each simulation dataset will grow to around 10 GB in around 6 months. Since the data is not related in any way I was thinking in separating each simulation into it's own database. That way it would be much easier for me to, at later date, move some of the databases to other servers (when dataset grows beyond the original server storage capacity limit). Do you intend to run queries across multiple simulations at once? If yes, you want to avoid multi databases. Other than that, I'd go with a naming convention like samples_simulation id and maybe some inheritance to ease querying multiple simulations. Nope, those 'realms' are completely separated, I'll just have hundreds of them. But each of them is in it's separate 'universe', they're not aware of each other in any way (i might be creating some statistics, but that is going to be really rarely). Mario -- 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 INTO large FKyed table is slow
On 11/29/2010 05:47 PM, Pierre C wrote: realm_51=# vacuum analyze verbose drones; INFO: vacuuming public.drones INFO: scanned index drones_pk to remove 242235 row versions DETAIL: CPU 0.02s/0.11u sec elapsed 0.28 sec. INFO: drones: removed 242235 row versions in 1952 pages DETAIL: CPU 0.01s/0.02u sec elapsed 0.03 sec. INFO: index drones_pk now contains 174068 row versions in 721 pages DETAIL: 107716 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. As you can see your index contains 174068 active rows and 242235 dead rows that probably should have been removed a long time ago by autovacuum, but you seem to have it turned off. It does not take a long time to vacuum this table (only 0.3 sec) so it is not a high cost, you should enable autovacuum and let it do the job (note that this doesn't stop you from manual vacuuming after big updates). Yes, you're right. I was doing some testing and I neglected to enable vacuuming after inserts. But what this shows is that table drones is having dead rows, and that table does get updated a lot. However, I don't have any performance problems here. The UPDATE takes no more than 10 seconds even if I update 50k (out of 150k) rows. I disabled autovacuum because I got a lot of WARNING: pgstat wait timeout and I could see the autovacuum job (pg_stat_activity) running during the run of the plpgsql function that handles inserts. I left the autovacuum off but I do VACUUM after each CSV insert. good 0 index pages have been deleted, 0 are currently reusable. CPU 0.38s/0.12u sec elapsed 16.56 sec. INFO: drones_history: found 0 removable, 16903164 nonremovable row versions in 129866 out of 195180 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 2.00s/1.42u sec elapsed 49.24 sec. good INFO: vacuuming pg_toast.pg_toast_2695510 INFO: index pg_toast_2695510_index now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_2695510: found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. same as above, no toast Yes. Just to make things clear, I never update/delete drones_history. I just INSERT, and every now and then I'll be doing SELECTs. realm_51=# select version(); version - PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 32-bit (1 row) Mario ok Try this : CLUSTER drones_pkey ON drones; Then check if your slow query gets a bit faster. If it does, try : ALTER TABLE drones SET ( fillfactor = 50 ); ALTER INDEX drones_pkey SET ( fillfactor = 50 ); CLUSTER drones_pkey ON drones; (again) This will make the updates on this table less problematic. VACUUM it after each mass update. Is this going to make any difference considering slow insert on drones_history? Because INSERTs/UPDATEs on drones tables are fast. The only noticable difference is that drones is 150k rows 'large' and drones_history has around 25M rows: realm_51=# select count(*) from drones_history ; count -- 25550475 (1 row) Mario -- 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 INTO large FKyed table is slow
On 11/29/2010 05:53 PM, Pierre C wrote: Yes, since (sample_id, drone_id) is primary key, postgres created composite index on those columns. Are you suggesting I add two more indexes, one for drone_id and one for sample_id? (sample_id,drone_id) covers sample_id but if you make searches on drone_id alone it is likely to be very slow since you got a large number of sample_ids. Postgres can use any column of a multicolumn index but it is only interesting performance-wise if the cardinality of the first (ignored) columns is low. If you often make searches on drone_id, create an index. But this isn't what is slowing your foreign key checks. Again, you have a point there. When I get to SELECTs to the history table I'll be doing most of the filtering on the drone_id (but also on sample_id, because I'll seldom drill all the way back in time, I'll be interested in just some periods), so I'll take this into consideration. But, as you've said, that's not what it's slowing my FK checks. Also it would be worthwhile for you to post the output of: EXPLAIN ANALYZE INSERT INTO drones_history (sample_id, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM tmpUpdate; to the list, so we can see what is taking the time. Is there a way to do so inside plpgsql function? I can recreate the whole process within psql and then post the explain analyze, it would just take me some time to do so. I'll post as soon as I'm done. Yes, this would be interesting. So, I did. I run the whole script in psql, and here is the result for the INSERT: realm_51=# explain analyze INSERT INTO drones_history (2771, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM tmp_drones_history; QUERY PLAN -- Seq Scan on tmp_drones_history (cost=0.00..81.60 rows=4160 width=48) (actual time=0.008..5.296 rows=5150 loops=1) Trigger for constraint drones_history_fk__drones: time=92.948 calls=5150 Total runtime: 16779.644 ms (3 rows) Now, this is only 16 seconds. In this 'batch' I've inserted 5150 rows. The batch before, I run that one 'the usual way', it inserted 9922 rows, and it took 1 minute and 16 seconds. I did not, however, enclose the process into begin/end. So, here are results when I, in psql, first issued BEGIN: realm_51=# explain analyze INSERT INTO drones_history (2772, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM tmp_drones_history; QUERY PLAN -- Seq Scan on tmp_drones_history (cost=0.00..79.56 rows=4056 width=48) (actual time=0.008..6.490 rows=5059 loops=1) Trigger for constraint drones_history_fk__drones: time=120.224 calls=5059 Total runtime: 39658.250 ms (3 rows) Time: 39658.906 ms Mario -- 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 INTO large FKyed table is slow
On 11/30/2010 05:26 PM, Mladen Gogala wrote: At the beginning of the load, you should defer all of the deferrable constraints, setting constraints deferred and issuing the copy statement within a transaction block, like this: scott=# begin; BEGIN Time: 0.203 ms scott=# set constraints all deferred; SET CONSTRAINTS Time: 0.201 ms scott=# copy test1 from '/tmp/test1.csv'; COPY 100 Time: 11.939 ms scott=# commit; ERROR: insert or update on table test1 violates foreign key constraint fk_tst1_deptno DETAIL: Key (col1)=(1) is not present in table dept. Of course, that will require complete rewrite of your load script, because the errors will be checked at the commit time and transaction can either fail as a whole or succeed as a whole. It's all or nothing Well, it is like that now. First I load the data from the CSV into the temporary table (just named temporary, exists on the server). That table is usualy aroun 10k rows. Then I call the function which does the job. situation. How frequently do you see records with an incorrect drone_id? Seldom. If that happens only once in a blue moon, you may need no stinkin' foreign keys in the first place, you may be able to have a batch job that will flag all the records with an invalid drone_id instead. I did have that idea, yes, but still, I'd like to know what is slowing postgres down. Because when I look at the disk I/O, it seems very random - i get around 800k of disk reads and ocasionaly 1500k of writes (during insert into history table). Furthermore, you can make sure that you have enough shared buffers to cache the entire drones table. Also, do strace on the postgres process handling your session and see whether the time is spent writing to WAL archives. If that is slowing you down, you should consider buying a SSD or a high end disk drive. I have never had such problem, but you should also check whether pg_loader can do anything for you. As far as speed is concerned, inserting with deferred foreign keys is almost as fast as inserting without foreign keys: scott=# alter table test1 drop constraint fk_tst1_deptno; ALTER TABLE Time: 16.219 ms scott=# copy test1 from '/tmp/test1.csv'; COPY 100 Time: 10.418 ms If you take a look at the example above, you will see that inserting with a deferred FK took 11.939 milliseconds while inserting into the same table without the FK took 10.418 milliseconds, the difference of 1.5 milliseconds per 100 rows. The timing of 2 seconds per 100 rows looks suspiciously high. Me thinks that your problem is not just the foreign key, there must be something else devouring the time. You should have a test instance, compiled with -g option and do profiling. I'll have to. So far I've been doing this only on that dedicated server. I'll try to download the database to my desktop and try the tests there. Concerning the shared_buffers, it's 256M, and the drones table is just 15M. I have tried your recommendation and it yielded no difference. Now I tried removing the constraints from the history table (including the PK) and the inserts were fast. After few 'rounds' of inserts I added constraints back, and several round after that were fast again. But then all the same. Insert of some 11k rows took 4 seconds (with all constraints) and now the last one of only 4k rows took one minute. I did vacuum after each insert. Mario -- 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 INTO large FKyed table is slow
On 11/28/2010 10:50 PM, Pierre C wrote: I pasted DDL at the begining of my post. Ah, sorry, didn't see it ;) The only indexes tables have are the ones created because of PK constraints. Table drones has around 100k rows. Table drones_history has around 30M rows. I'm not sure what additional info you'd want but I'll be more than happy to provide more relevant information. Can you post the following : - pg version - output of VACCUM ANALYZE VERBOSE for your 2 tables Here it is: realm_51=# vacuum analyze verbose drones; INFO: vacuuming public.drones INFO: scanned index drones_pk to remove 242235 row versions DETAIL: CPU 0.02s/0.11u sec elapsed 0.28 sec. INFO: drones: removed 242235 row versions in 1952 pages DETAIL: CPU 0.01s/0.02u sec elapsed 0.03 sec. INFO: index drones_pk now contains 174068 row versions in 721 pages DETAIL: 107716 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: drones: found 486 removable, 174068 nonremovable row versions in 1958 out of 1958 pages DETAIL: 0 dead row versions cannot be removed yet. There were 64 unused item pointers. 0 pages are entirely empty. CPU 0.22s/0.90u sec elapsed 22.29 sec. INFO: vacuuming pg_toast.pg_toast_2695558 INFO: index pg_toast_2695558_index now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_2695558: found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing public.drones INFO: drones: scanned 1958 of 1958 pages, containing 174068 live rows and 0 dead rows; 174068 rows in sample, 174068 estimated total rows VACUUM realm_51=# vacuum analyze verbose drones_history; INFO: vacuuming public.drones_history INFO: index drones_history_pk now contains 25440352 row versions in 69268 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.38s/0.12u sec elapsed 16.56 sec. INFO: drones_history: found 0 removable, 16903164 nonremovable row versions in 129866 out of 195180 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 2.00s/1.42u sec elapsed 49.24 sec. INFO: vacuuming pg_toast.pg_toast_2695510 INFO: index pg_toast_2695510_index now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_2695510: found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing public.drones_history INFO: drones_history: scanned 195180 of 195180 pages, containing 25440352 live rows and 0 dead rows; 60 rows in sample, 25440352 estimated total rows VACUUM realm_51=# select version(); version - PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 32-bit (1 row) Mario -- 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 INTO large FKyed table is slow
On 11/29/2010 08:11 AM, Mark Kirkwood wrote: On 29/11/10 00:46, Mario Splivalo wrote: This is the slow part: INSERT INTO drones_history (sample_id, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM tmpUpdate; For 100 rows this takes around 2 seconds. For 1000 rows this takes around 40 seconds. For 5000 rows this takes around 5 minutes. For 50k rows this takes around 30 minutes! Now this is where I start lag because I get new CSV every 10 minutes or so. Have you created indexes on drones_history(sample_id) and drones_history(drone_id)? That would probably help speed up your INSERT quite a bit if you have not done so. Yes, since (sample_id, drone_id) is primary key, postgres created composite index on those columns. Are you suggesting I add two more indexes, one for drone_id and one for sample_id? Also it would be worthwhile for you to post the output of: EXPLAIN ANALYZE INSERT INTO drones_history (sample_id, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM tmpUpdate; to the list, so we can see what is taking the time. Is there a way to do so inside plpgsql function? I can recreate the whole process within psql and then post the explain analyze, it would just take me some time to do so. I'll post as soon as I'm done. Mario -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Simple database, multiple instances?
I have simple database schema, containing just three tables: samples, drones, drones_history. Now, those tables hold data for the drones for a simulation. Each simulation dataset will grow to around 10 GB in around 6 months. Since the data is not related in any way I was thinking in separating each simulation into it's own database. That way it would be much easier for me to, at later date, move some of the databases to other servers (when dataset grows beyond the original server storage capacity limit). But. At this time I have around 600 simulations, that would mean creating 600 databases, and in future there could very well be around 5000 simulations. Is postgres going to have 'issues' with that large number of databases? Or do I model my system in a way that each database holds around 100 simulations? Mario -- 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 INTO large FKyed table is slow
The database for monitoring certain drone statuses is quite simple: CREATE TABLE samples ( sample_id integer not null primary key, sample_timestamp timestamp not null default now() ); CREATE TABLE drones ( drone_id integer not null primary key, drone_log_notice character varying, crone_coordinates point not null, drone_temperature float, drone_pressure float ); CREATE TABLE drones_history ( drone_id integer not null, sample_id integer not null, drone_log_notice character varying, drone_temperature float, drone_pressure float, constraint drones_history_pk primary key (drone_id, sample_id), constraint drones_history_fk__samples foreign key (sample_id) references samples(sample_id), constraint drones_history_fk__drones foreign key (drone_id) references drones(drone_id) ); Every ten to twenty minutes I receive CSV file with most of the drones statuses. CSV file includes data for new drones, if they're put into use. When I receive new data I load whole CSV file to a database, then call stored procedure that 'deals' with that data. So far I have around 6000 samples, around 160k drones and drones_history is around 25M rows. The CSV file contains around 15k-20k of 'rows', mostly data about old drones. Every now and then (on every 5th - 10th CSV-insert) there is data with around 1000-5000 new drones. Here is what I do in stored procedure, after i COPYed the data from the CSV to temporary.drones table: First, I create temporary table, inside the procedure, that holds rows for the new drones: CREATE TEMPORARY TABLE tmpNew ON COMMIT DROP AS SELECT drone_id, log_notice, coord_x, coord_y, temp, press FROM temp.drones WHERE NOT EXISTS (SELECT 1 FROM public.drones WHERE public.drones.drone_id = temporary.drone.drone_id); This is done in miliseconds, even if the count for the new drones is large (i've tested it with 10k new drones although I real-life action I'd never get more thatn 5k new drones per CSV). INSERT INTO public.drones (drone_id, drone_log_notice, coordinates, drone_temperature, drone_temperature) SELECT drone_id, log_notice, point(coord_x, coord_y) as coordinates, temp, press FROM tmpNew; INSERT INTO public.drones_history (sample_id, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT a_sample_id, drone_id, log_notice, temp, pressue FROM tmpNew; This is also done in miliseconds. Now, I 'update' data for the existing drones, and fill in the history table on those drones. First I create temporary table with just the changed rows: CREATE TEMPORARY TABLE tmpUpdate ON COMMIT DROP AS SELECT a_batch_id, t.drone_id, t.log_notice, t.temp, t.press FROM temporary.drones t JOIN public.drones p ON t.drone_id = p.drone_id WHERE p.drone_log_notice != t.log_notice OR p.temp != t.temp OR p.press != t.press; Now, that part is also fast. I usualy have around 100-1000 drones that changed 'state', but sometimes I get even half of the drones change states (around 50k) and creation of the tmpUpdate takes no more than ten to twenty milliseconds. This is the slow part: INSERT INTO drones_history (sample_id, drone_id, drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM tmpUpdate; For 100 rows this takes around 2 seconds. For 1000 rows this takes around 40 seconds. For 5000 rows this takes around 5 minutes. For 50k rows this takes around 30 minutes! Now this is where I start lag because I get new CSV every 10 minutes or so. And the last part is to upadte the actual drones table: UPDATE public.drones p SET drone_log_notice = t.log_notice, drone_temperature = t.temp, drone_pressure = t.press FROM temporary.drones t WHERE t.drone_id = p.drone_id AND (t.log_notice != p.drone_log_notice OR t.temp != p.drone_temperature OR p.press != t.drone_pressure); This is also very fast, even when almost half the table is updated the UPDATE takes around 10 seconds. Usualy it's around 30-50 ms. The machine I'm doing this has 4 GB of RAM, dual-Xeon something (3GHz). Two SAS drives in mirror, capable of around 100 MB/s in sequential r/w (i know it means nothing, but just to get an idea). Database is around 2 GB is size (pg_database_size). When I dump/recreate the database I can speedup things a bit, but after half day of operations the INSERTs are slow again. When I do dump/restore of the database I get around 40/50 MB/sec reding/writing from the disk (COPYing data, PK/FK constraints creation), but when that INSERT gets stuck io-wait goes to skies - iostat shows that Postgres is mainly reading from the disks, around 800k/sec - 1024k/sec. I've set shared_buffers to 256M, work_mem to 96M, wal_buffers to 16M and checkpoint_segments to 16. I've turned off autovaccum, I do analyze/vacuum after each insert-job is done, after TRUNCATEing temporary.drones table. Out of despair I tried to set fsync=off, but that gave me just a small
Re: [PERFORM] SELECT INTO large FKyed table is slow
On 11/28/2010 07:56 PM, Pierre C wrote: When I remove foreign constraints (drones_history_fk__samples and drones_history_fk__drones) (I leave the primary key on drones_history) than that INSERT, even for 50k rows, takes no more than a second. So, my question is - is there anything I can do to make INSERTS with PK faster? Or, since all the reference checking is done inside the procedure for loading data, shall I abandon those constraints entirely? Mario Maybe... or not. Can you post details about : - the foreign keys - the tables that are referred to (including indexes) I pasted DDL at the begining of my post. The only indexes tables have are the ones created because of PK constraints. Table drones has around 100k rows. Table drones_history has around 30M rows. I'm not sure what additional info you'd want but I'll be more than happy to provide more relevant information. CREATE TABLE foo (x INTEGER PRIMARY KEY); I generate_series( 1,10 ); Temps : 766,182 ms test= VACUUM ANALYZE foo; Temps : 71,938 ms test= CREATE TABLE bar ( x INTEGER REFERENCES foo(x) ); CREATE TABLE test= INSERT INTO bar SELECT * FROM generate_series( 1,10 ); Temps : 2834,430 ms As you can see, 100.000 FK checks take less than 3 seconds on this very simple example. There is probably something that needs fixing. Yes, when the FKyed table is small enough inserts are quite fast. But when they grow larger the whole system slows down. I just repeated your test and I'm getting similar results - on my desktop. I'll try to assemble some code to recreate workload and see if I'll run into same problems. Mario -- 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] Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Scott Marlowe wrote:
It's not really solved, it's just a happy coincidence that the current
plan runs well. In order to keep the query planner making good
choices you need to increase stats target for the field in the index
above. The easiest way to do so is to do this:
alter database mydb set default_statistics_target=100;
and run analyze again:
analyze;
So, i removed the index on field_name, set
default_default_statistics_target to 100, analyzed, and the results are
the same:
QUERY PLAN
Hash Join (cost=1.04..58676.31 rows=218048 width=68) (actual
time=0.067..12268.394 rows=6 loops=1)
Hash Cond: ((u.field_name)::text = (t.key)::text)
- Seq Scan on photo_info_data u (cost=0.00..47500.30 rows=2398530
width=50) (actual time=0.013..6426.611 rows=2398446 loops=1)
- Hash (cost=1.02..1.02 rows=2 width=18) (actual
time=0.015..0.015 rows=2 loops=1)
- Seq Scan on t_query_data t (cost=0.00..1.02 rows=2
width=18) (actual time=0.002..0.006 rows=2 loops=1)
Total runtime: 12268.459 ms
(6 rows)
I even changed default_statistics_target to 1000:
Hash Join (cost=1.04..58580.29 rows=208561 width=67) (actual
time=0.054..12434.283 rows=6 loops=1)
Hash Cond: ((u.field_name)::text = (t.key)::text)
- Seq Scan on photo_info_data u (cost=0.00..47499.46 rows=2398446
width=49) (actual time=0.012..6129.923 rows=2398446 loops=1)
- Hash (cost=1.02..1.02 rows=2 width=18) (actual
time=0.015..0.015 rows=2 loops=1)
- Seq Scan on t_query_data t (cost=0.00..1.02 rows=2
width=18) (actual time=0.002..0.004 rows=2 loops=1)
Total runtime: 12434.338 ms
(6 rows)
Even when I run this query, I get sequential scan:
explain analyze select * from photo_info_data where field_name =
'f-spot' or field_name = 'shutter';
QUERY PLAN
---
Seq Scan on photo_info_data (cost=0.00..59491.69 rows=1705 width=49)
(actual time=0.018..1535.963 rows=6 loops=1)
Filter: (((field_name)::text = 'f-spot'::text) OR
((field_name)::text = 'shutter'::text))
Total runtime: 1536.010 ms
(3 rows)
These are the representations of te values 'f-spot' and 'shutter' for
the field field_name in photo_info_data table:
xmltest=# select field_name, count(*) from user_info_data where
field_name in ('visina', 'spol') group by field_name;
field_name | count
+---
'f-spot' | 3
'shutter' | 3
(2 rows)
Maybe my test-data is poor? As I've mentioned, photo_info_data has
little over 230 rows. And this is complete 'distribution' of the data:
xmltest=# select field_name, count(*) from user_info_data group by
field_name order by count(*) desc;
field_name | count
+
field_Xx1 | 35
field_Xx2 | 332447
field_Xx3 | 297414
field_Xx4 | 262394
field_Xx5 | 227396
field_Xx6 | 192547
field_Xx7 | 157612
field_Xx8 | 122543
field_Xx9 | 87442
field_Xx10 | 52296
field_1| 5
field_2| 47389
field_3| 42412
field_4| 37390
field_5| 32366
field_6| 27238
field_7| 22360
field_Xx11 | 17589
field_8| 17412
field_9| 12383
field_10 | 7386
field_11 | 2410
f-spot | 3
shutter| 3
focal | 3
flash | 3
m_city | 3
person | 3
iso| 2
(29 rows)
No matter what field_name value I enter in WHERE condition, planner
chooses sequential scan. Only when I add seperate index on field_name,
planner chooes index scan or bitmap index scan.
Mike
--
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] Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Scott Marlowe wrote: On Mon, Apr 6, 2009 at 6:20 AM, Mario Splivalo mario.spliv...@megafon.hr wrote: Scott Marlowe wrote: It's not really solved, it's just a happy coincidence that the current plan runs well. In order to keep the query planner making good choices you need to increase stats target for the field in the index above. The easiest way to do so is to do this: alter database mydb set default_statistics_target=100; and run analyze again: analyze; So, i removed the index on field_name, set default_default_statistics_target to 100, analyzed, and the results are the same: Why did you remove the index? Because I already have index on that column, index needed to enforce PK constraint. Here is the original DDL for the table: CREATE TABLE photo_info_data ( photo_id integer NOT NULL, field_name character varying NOT NULL, field_value character varying, CONSTRAINT photo_info_data_pk PRIMARY KEY (user_id, field_name) ) CREATE INDEX photo_info_data_ix_field_value ON user_info_data USING btree (field_value); So, there is index on (user_id, field_name). Postgres is using index for user_id (...WHERE user_id = 12345) but not on field-name (...WHERE field_name = 'f-spot'). When I add extra index on field name: CREATE INDEX photo_info_data_ix__field_name ON user_info_data USING btree (field_name); Then that index is used. Mike -- 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] Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Scott Marlowe wrote: CREATE INDEX photo_info_data_ix_field_value ON user_info_data USING btree (field_value); So, there is index on (user_id, field_name). Postgres is using index for user_id (...WHERE user_id = 12345) but not on field-name (...WHERE field_name = 'f-spot'). When I add extra index on field name: CREATE INDEX photo_info_data_ix__field_name ON user_info_data USING btree (field_name); Then that index is used. On older versions of pgsql, the second of two terms in a multicolumn index can't be used alone. On newer versions it can, but it is much less efficient than if it's a single column index or if the term is the first one not the second. I'm using 8.3.7. So, you'd also suggest to keep that extra (in a way redundant) index on field_name, since I need PK on (photo_id, field_name) ? Mike -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Forcing seq_scan off for large table joined with tiny table yeilds improved performance
I have two tables, like this: Big table: CREATE TABLE photo_info_data ( photo_id integer NOT NULL, field_name character varying NOT NULL, field_value character varying, CONSTRAINT photo_info_data_pk PRIMARY KEY (photo_id, field_name) ) WITH (OIDS=FALSE); CREATE INDEX user_info_data_ix_field_value ON user_info_data USING btree (field_value); Small table: CREATE TABLE t_query_data ( i integer, key character varying, op character varying, value character varying ) WITH (OIDS=FALSE); I have around 240 rows in photo_info_data, and just two rows in t_query_data: i | key | op | value ---+--++ 1 | f-stop | eq | 2.6 2 | shutter | gt | 1/100 This is the query I'm executing: SELECT * FROM photo_info_data u JOIN t_query_data t on u.field_name = key This query takes around 900ms to execute. It returns 6 rows. When I do 'explain analyze' for some reason it takes around 7 seconds, and this is what I get: phototest=# explain analyze select * from photo_info_data u join t_query_data t on u.field_name = key; QUERY PLAN Hash Join (cost=1.04..58676.31 rows=218048 width=68) (actual time=2381.895..7087.225 rows=6 loops=1) Hash Cond: ((u.field_name)::text = (t.key)::text) - Seq Scan on photo_info_data u (cost=0.00..47500.30 rows=2398530 width=50) (actual time=0.042..3454.112 rows=2398446 loops=1) - Hash (cost=1.02..1.02 rows=2 width=18) (actual time=0.016..0.016 rows=2 loops=1) - Seq Scan on t_query_data t (cost=0.00..1.02 rows=2 width=18) (actual time=0.003..0.007 rows=2 loops=1) Total runtime: 7087.291 ms (6 rows) Time: 7088.663 ms I can rerun this query many times, it's always around 7 seconds. I/O wait during the query is nonexistant, it just takes 100% of CPU time (i have a DualCore Opteron server). If I force the planner not to use sequential_scan, here is what I get: phototest=# explain analyze select * from photo_info_data u join t_query_data t on u.field_name = key; QUERY PLAN - Nested Loop (cost=100039134.84..100130206.79 rows=218048 width=68) (actual time=271.138..540.998 rows=6 loops=1) - Seq Scan on t_query_data t (cost=1.00..10001.02 rows=2 width=18) (actual time=0.008..0.015 rows=2 loops=1) - Bitmap Heap Scan on photo_info_data u (cost=39134.84..63740.08 rows=109024 width=50) (actual time=270.464..270.469 rows=3 loops=2) Recheck Cond: ((u.field_name)::text = (t.key)::text) - Bitmap Index Scan on photo_info_data_pk (cost=0.00..39107.59 rows=109024 width=0) (actual time=270.435..270.435 rows=3 loops=2) Index Cond: ((u.field_name)::text = (t.key)::text) Total runtime: 541.065 ms (7 rows) Time: 542.147 ms The database currently has only those two tables. I have vacuumed them prior running above queries. I tought this information also might be important: phototest=# select key, count(*) from photo_info_data u join t_query_data t on u.field_name = key group by key; key | count --+--- f-stop | 3 shutter | 3 (2 rows) Am I doing something wrong here? The photo_info_data would hold around 10.000.000 records, should I be doing 'set seq_scan to false' each time I will want to run this query? (Since I'm accessing postgres trough JDBC I'll have same situation I had weeks ago, I described it here also). Mike -- 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] Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Tom Lane wrote: Mario Splivalo mario.spliv...@megafon.hr writes: - Bitmap Heap Scan on photo_info_data u (cost=39134.84..63740.08 rows=109024 width=50) (actual time=270.464..270.469 rows=3 loops=2) Recheck Cond: ((u.field_name)::text = (t.key)::text) - Bitmap Index Scan on photo_info_data_pk (cost=0.00..39107.59 rows=109024 width=0) (actual time=270.435..270.435 rows=3 loops=2) Index Cond: ((u.field_name)::text = (t.key)::text) You need to figure out why that rowcount estimate is off by more than four orders of magnitude :-( Huh, thnx! :) Could you give me some starting points, what do I do? Could it be because table is quite large, and there are only 3 columns that match join condition? Now, after I finished writing above lines, index creation on photo_info_data(field_name) was done. When I rerun above query, here is what I get: phototest=# explain analyze select field_name, count(*) from t_query_data t join photo_info_data u on t.key = u.field_name group by field_name; QUERY PLAN -- HashAggregate (cost=57414.33..57414.61 rows=22 width=9) (actual time=0.135..0.139 rows=2 loops=1) - Nested Loop (cost=2193.50..56324.09 rows=218048 width=9) (actual time=0.063..0.114 rows=6 loops=1) - Seq Scan on t_query_data t (cost=0.00..1.02 rows=2 width=6) (actual time=0.019..0.022 rows=2 loops=1) - Bitmap Heap Scan on photo_info_data u (cost=2193.50..26798.74 rows=109024 width=9) (actual time=0.025..0.030 rows=3 loops=2) Recheck Cond: ((u.field_name)::text = (t.key)::text) - Bitmap Index Scan on photo_info_data_ix__field_name (cost=0.00..2166.24 rows=109024 width=0) (actual time=0.019..0.019 rows=3 loops=2) Index Cond: ((u.field_name)::text = (t.key)::text) Total runtime: 0.200 ms (8 rows) So, I guess I solved my problem! :) The explain analyze still shows that row estimate is 'quite off' (109024 estimated vs only 3 actuall), but the query is light-speeded :) I tought that having primary key (and auto-index because of primary key) on (photo_id, field_name) should be enough. Now I have two indexes on field_name, but that seems to do good. Mike -- 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 much slower when run from postgres function
I have a function, looking like this: CREATE OR REPLACE FUNCTION get_memo_display_queue_size(a_service_id integer) RETURNS integer AS $BODY$ SELECT COUNT(*)::integer FROM v_messages_memo LEFT JOIN messages_memo_displayed ON id = message_id WHERE service_id = $1 AND state = 1 AND admin_id IS NULL; $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER COST 100; Now, when I run that function from psql, it takes around 200ms to complete: pulitzer2=# explain analyze select get_memo_display_queue_size(1829); QUERY PLAN Result (cost=0.00..0.26 rows=1 width=0) (actual time=219.728..219.730 rows=1 loops=1) Total runtime: 219.758 ms (2 rows) pulitzer2=# And it takes around 200ms each time I run the function! When I rewrite the query so I can see queryplan, I get this: create view _v1 as SELECT * FROM v_messages_memo LEFT JOIN messages_memo_displayed ON id = message_id WHERE state = 1 AND admin_id IS NULL; pulitzer2=# EXPLAIN ANALYZE select count(*) from _v1 WHERE service_id = 1829; QUERY PLAN Aggregate (cost=23506.14..23506.15 rows=1 width=0) (actual time=6.001..6.002 rows=1 loops=1) - Nested Loop (cost=150.69..23505.59 rows=216 width=0) (actual time=5.744..5.971 rows=13 loops=1) - Hash Left Join (cost=150.69..11035.16 rows=2104 width=4) (actual time=5.721..5.793 rows=13 loops=1) Hash Cond: (messages.id = messages_memo_displayed.message_id) Filter: (messages_memo_displayed.admin_id IS NULL) - Bitmap Heap Scan on messages (cost=97.03..10955.11 rows=4209 width=4) (actual time=0.042..0.075 rows=13 loops=1) Recheck Cond: (service_id = 1829) - Bitmap Index Scan on messages_uq__service_id__tan (cost=0.00..95.98 rows=4209 width=0) (actual time=0.032..0.032 rows=13 loops=1) Index Cond: (service_id = 1829) - Hash (cost=28.85..28.85 rows=1985 width=8) (actual time=5.666..5.666 rows=1985 loops=1) - Seq Scan on messages_memo_displayed (cost=0.00..28.85 rows=1985 width=8) (actual time=0.009..2.697 rows=1985 loops=1) - Index Scan using messages_memo_pk on messages_memo (cost=0.00..5.91 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=13) Index Cond: (messages_memo.message_id = messages.id) Filter: ((messages_memo.state)::integer = 1) Total runtime: 6.079 ms (15 rows) So I noticed that postgres is using seq_scan on messages_memo_displayed, although there is a PK (and an index) on message_id in messages_memo_displayed (I'll post DDL of the tables at the end of the post). So, I tried EXPLAIN ANALYZE after I forced planner not to use sequential scans: pulitzer2=# EXPLAIN ANALYZE select count(*) from _v1 WHERE service_id = 1829; QUERY PLAN - Aggregate (cost=25403.60..25403.61 rows=1 width=0) (actual time=6.546..6.547 rows=1 loops=1) - Nested Loop (cost=2048.16..25403.06 rows=216 width=0) (actual time=6.287..6.512 rows=13 loops=1) - Hash Left Join (cost=2048.16..12932.63 rows=2104 width=4) (actual time=6.268..6.340 rows=13 loops=1) Hash Cond: (messages.id = messages_memo_displayed.message_id) Filter: (messages_memo_displayed.admin_id IS NULL) - Bitmap Heap Scan on messages (cost=97.03..10955.11 rows=4209 width=4) (actual time=0.043..0.078 rows=13 loops=1) Recheck Cond: (service_id = 1829) - Bitmap Index Scan on messages_uq__service_id__tan (cost=0.00..95.98 rows=4209 width=0) (actual time=0.032..0.032 rows=13 loops=1) Index Cond: (service_id = 1829) - Hash (cost=1926.31..1926.31 rows=1985 width=8) (actual time=6.211..6.211 rows=1985 loops=1) - Index Scan using messages_memo_displayed_pk on messages_memo_displayed (cost=0.00..1926.31 rows=1985 width=8) (actual time=0.069..3.221 rows=1985 loops=1) - Index Scan using messages_memo_pk on messages_memo (cost=0.00..5.91 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=13) Index Cond: (messages_memo.message_id = messages.id) Filter: ((messages_memo.state)::integer = 1) Total runtime: 6.628 ms (15 rows) No sequential scan. So I 'changed' my function so that first row says 'SET enable_seqscan TO
Re: [PERFORM] Query much slower when run from postgres function
Tom Lane wrote: Mario Splivalo mario.spliv...@megafon.hr writes: Now I'm confused, why is 'sql' function much slower than 'direct' SELECT? Usually the reason for this is that the planner chooses a different plan when it has knowledge of the particular value you are searching for than when it does not. I suppose 'service_id' has a very skewed distribution and you are looking for an uncommon value? I don't think so. Here is distribution for the messages_memo_displayed table (joined with messages, just to show how many messages of each service_id are there in messages_memo_displayed): pulitzer2=# select service_id, count(*) from messages join messages_memo_displayed on id = message_id group by service_id order by service_id; service_id | count +--- 504 | 2 1790 | 1922 1814 | 1 1816 |57 1818 | 3 (5 rows) And the sizes of other tables involved: pulitzer2=# select count(*) from messages_memo_displayed; count --- 1985 (1 row) Time: 0.602 ms pulitzer2=# pulitzer2=# select count(*) from messages; count - 1096388 (1 row) Time: 345.267 ms pulitzer2=# select count(*) from messages_memo; count 776238 (1 row) Time: 133.942 ms pulitzer2=# As I've mentioned earlier, I have created an view, for the sake of this posting: CREATE OR REPLACE VIEW _v1 AS SELECT messages.id, messages.from, messages.to, messages.receiving_time, messages.raw_text, messages.keyword, messages.destination_id, messages.vpn_id, messages.service_id, messages.status, messages.gateway_message_id, messages.prize_id, messages.tan, messages_memo.memo, messages_memo.state, messages_memo.displayed, messages_memo_displayed.admin_id FROM messages JOIN messages_memo ON messages.id = messages_memo.message_id LEFT JOIN messages_memo_displayed ON messages.id = messages_memo_displayed.message_id WHERE messages_memo.state::integer = 1 AND messages_memo_displayed.admin_id IS NULL; And then I created a function: CREATE OR REPLACE FUNCTION __new__get_memo_display_queue_size(a_service_id integer) RETURNS integer AS $BODY$ SELECT COUNT(*)::int4 FROM _v1 WHERE service_id = $1 $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; Now, here are the differences: pulitzer2=# select count(*) from _v1 where service_id = 504; count --- 0 (1 row) Time: 6.101 ms pulitzer2=# select __new__get_memo_display_queue_size(504); __new__get_memo_display_queue_size 0 (1 row) Time: 322.555 ms pulitzer2=# select count(*) from _v1 where service_id = 1790; count --- 1 (1 row) Time: 25.203 ms pulitzer2=# select __new__get_memo_display_queue_size(1790); __new__get_memo_display_queue_size 1 (1 row) Time: 225.763 ms pulitzer2=# select count(*) from _v1 where service_id = 1814; count --- 2 (1 row) Time: 13.662 ms pulitzer2=# select __new__get_memo_display_queue_size(1814); __new__get_memo_display_queue_size 2 (1 row) Time: 215.251 ms pulitzer2=# select count(*) from _v1 where service_id = 1816; count --- 1 (1 row) Time: 10.111 ms pulitzer2=# select __new__get_memo_display_queue_size(1816); __new__get_memo_display_queue_size 1 (1 row) Time: 220.457 ms pulitzer2=# select count(*) from _v1 where service_id = 1829; count --- 13 (1 row) Time: 2.023 ms pulitzer2=# select __new__get_memo_display_queue_size(1829); __new__get_memo_display_queue_size 13 (1 row) Time: 221.956 ms pulitzer2=# Is this difference normal? I tend to have the interface between the database and the application trough functions, and I'd like not to include 'SELECT COUNT(*)...' in my Java code (at least, if I don't have to! - esp. because I'm not Java developer on the project). Then, this is also interesting, I think! I'm telling the planer never to use sequential scan: pulitzer2=# set enable_seqscan to false; SET Time: 0.150 ms pulitzer2=# select __new__get_memo_display_queue_size(1829); __new__get_memo_display_queue_size 13 (1 row) Time: 2.412 ms pulitzer2=# select count(*) from _v1 where service_id = 1829; count --- 13 (1 row) Time: 2.092 ms pulitzer2=# select __new__get_memo_display_queue_size(1816); __new__get_memo_display_queue_size 1 (1 row) Time: 2.473 ms pulitzer2=# select count(*) from _v1 where service_id = 1816; count --- 1 (1 row) Time: 2.117 ms pulitzer2=# Now the the execution times are almost the same. So, why this difference in the first place, and, what should I do to have satisfying
Re: [PERFORM] Query much slower when run from postgres function
Guillaume Cottenceau wrote: Now I'm confused, why is 'sql' function much slower than 'direct' SELECT? Usually the reason for this is that the planner chooses a different plan when it has knowledge of the particular value you are searching for than when it does not. Yes, and since Mario is coming from JDBC, I'll share my part on this: I also noticed some very wrong plans in JDBC because of the optimization in prepared statements consisting of planning once for all runs, e.g. without any parameter values to help planning. For what is worth: When I call postgres function via JDBC, I have almost the same execution time as when calling function from psql. When I call SELECT COUNT(*)... WHERE... query from JDBC, I again have almost the same execution time as when executing query from psql. Postgres function takes around 200ms, and SELECT query takes around 2-4ms. Mike -- 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 much slower when run from postgres function
Tom Lane wrote: Mario Splivalo mario.spliv...@megafon.hr writes: Is this difference normal? It's hard to tell, because you aren't comparing apples to apples. Try a prepared statement, like [...cut...] which should produce results similar to the function. You could then use explain analyze execute to probe further into what's happening. Huh, thnx! :) This got me even more confused: pulitzer2=# prepare foo(int) as select count(*) from _v1 where service_id = $1; PREPARE Time: 4.425 ms pulitzer2=# execute foo(1816); count --- 1 (1 row) Time: 248.301 ms pulitzer2=# select __new__get_memo_display_queue_size(1816); __new__get_memo_display_queue_size 1 (1 row) Time: 218.914 ms pulitzer2=# So, it is the same. When I do EXPLAIN ANALYZE EXECUTE I get completely different execution plan: pulitzer2=# explain analyze execute foo(1816); QUERY PLAN Aggregate (cost=40713.22..40713.23 rows=1 width=0) (actual time=475.649..475.650 rows=1 loops=1) - Hash Join (cost=21406.91..40711.65 rows=626 width=0) (actual time=183.004..475.629 rows=1 loops=1) Hash Cond: (messages_memo.message_id = messages.id) - Seq Scan on messages_memo (cost=0.00..18630.83 rows=106825 width=4) (actual time=0.083..324.607 rows=107608 loops=1) Filter: ((state)::integer = 1) - Hash (cost=21326.61..21326.61 rows=6424 width=4) (actual time=5.868..5.868 rows=5 loops=1) - Hash Left Join (cost=341.64..21326.61 rows=6424 width=4) (actual time=5.650..5.855 rows=5 loops=1) Hash Cond: (messages.id = messages_memo_displayed.message_id) Filter: (messages_memo_displayed.admin_id IS NULL) - Bitmap Heap Scan on messages (cost=287.98..21192.42 rows=12848 width=4) (actual time=0.049..0.169 rows=62 loops=1) Recheck Cond: (service_id = $1) - Bitmap Index Scan on messages_uq__service_id__tan (cost=0.00..284.77 rows=12848 width=0) (actual time=0.038..0.038 rows=62 loops=1) Index Cond: (service_id = $1) - Hash (cost=28.85..28.85 rows=1985 width=8) (actual time=5.564..5.564 rows=1985 loops=1) - Seq Scan on messages_memo_displayed (cost=0.00..28.85 rows=1985 width=8) (actual time=0.008..2.674 rows=1985 loops=1) Total runtime: 475.761 ms (16 rows) Time: 476.280 ms pulitzer2=# There is a sequential scan on messages_memo, a scan that doesn't show up when I just do 'SELECT COUNT(*)...'. When I do 'set enable_seqscan to false' before i do PREPARE, here is the execution plan: pulitzer2=# explain analyze execute foo(1816); QUERY PLAN - Aggregate (cost=55624.91..55624.92 rows=1 width=0) (actual time=7.122..7.123 rows=1 loops=1) - Nested Loop (cost=2239.11..55623.34 rows=626 width=0) (actual time=7.098..7.108 rows=1 loops=1) - Hash Left Join (cost=2239.11..23224.07 rows=6424 width=4) (actual time=6.663..6.962 rows=5 loops=1) Hash Cond: (messages.id = messages_memo_displayed.message_id) Filter: (messages_memo_displayed.admin_id IS NULL) - Bitmap Heap Scan on messages (cost=287.98..21192.42 rows=12848 width=4) (actual time=0.138..0.373 rows=62 loops=1) Recheck Cond: (service_id = $1) - Bitmap Index Scan on messages_uq__service_id__tan (cost=0.00..284.77 rows=12848 width=0) (actual time=0.121..0.121 rows=62 loops=1) Index Cond: (service_id = $1) - Hash (cost=1926.31..1926.31 rows=1985 width=8) (actual time=6.430..6.430 rows=1985 loops=1) - Index Scan using messages_memo_displayed_pk on messages_memo_displayed (cost=0.00..1926.31 rows=1985 width=8) (actual time=0.063..3.320 rows=1985 loops=1) - Index Scan using messages_memo_pk on messages_memo (cost=0.00..5.03 rows=1 width=4) (actual time=0.025..0.025 rows=0 loops=5) Index Cond: (messages_memo.message_id = messages.id) Filter: ((messages_memo.state)::integer = 1) Total runtime: 7.260 ms (15 rows) Time: 7.786 ms I have no idea why postgres chooses sequential scan over messages_memo when I PREPARE the select. For now I'll go with plpgsql function (instead of sql function) because I can do 'set enable_seqscan to true' just before RETURNing the value. That way, when I call the function via JDBC I have short execution times. Mike -- Sent via pgsql-performance mailing list (pgsql
Re: [PERFORM] Postgres not willing to use an index?
Tom Lane wrote: Hardly surprising --- a search on the index's lowest-order column would require scanning practically all of the index. (If you think about the ordering of the index entries you'll see why.) If this is a typical query then you need a separate index on transaction_time_commit. Yes, actually I just moved transaction_time_commit column to the begining of the index, since, most of the time I run queries based on transaction_time_commit and then transaction_client_id and transaction_destination_id. The fine manual goes into some detail about how to design indexes; http://www.postgresql.org/docs/8.3/static/indexes.html particularly 11.3, 11.5. I see it now. I read the manual concerning CREATE INDEX command, and there is no mention of multicolumn indices, did not notice Note that points to Chapter 11. Mike -- 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 not willing to use an index?
Robert Haas wrote: On Fri, Feb 6, 2009 at 12:41 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: What's weird about this example is that when he sets enable_seqscan to off, the bitmap index scan plan is actually substantially faster, even though it in fact does scan nearly the entire heap. I don't understand how it can be faster to scan the index and the heap than to just scan the heap. It's cached in the second test, maybe? I gather that the results were repeatable, but perhaps Mario could double-check that? I think that it is always cached - the machine has 4GB of RAM, and i'm just using it for testing. Now, I think that the cache is used because there is no I/O wait when I run the queries (if you have any suggestion on how to check cache usage, since I have no idea): jura=# set enable_seqscan to true; SET jura=# explain analyze select * from transactions where transaction_time_commit between '2008-01-01' and '2008-01-31 23:59:59'; QUERY PLAN - Seq Scan on transactions (cost=0.00..418365.68 rows=759775 width=91) (actual time=928.342..3788.232 rows=722176 loops=1) Filter: ((transaction_time_commit = '2008-01-01 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit = '2008-01-31 23:59:59+01'::timestamp with time zone)) Total runtime: 3936.744 ms (3 rows) jura=# set enable_seqscan to false; SET jura=# explain analyze select * from transactions where transaction_time_commit between '2008-01-01' and '2008-01-31 23:59:59'; QUERY PLAN --- Bitmap Heap Scan on transactions (cost=428882.89..651630.52 rows=759775 width=91) (actual time=1358.040..1633.867 rows=722176 loops=1) Recheck Cond: ((transaction_time_commit = '2008-01-01 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit = '2008-01-31 23:59:59+01'::timestamp with time zone)) - Bitmap Index Scan on transactions_idx__client_data (cost=0.00..428692.95 rows=759775 width=0) (actual time=1354.485..1354.485 rows=722176 loops=1) Index Cond: ((transaction_time_commit = '2008-01-01 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit = '2008-01-31 23:59:59+01'::timestamp with time zone)) Total runtime: 1778.938 ms (5 rows) Now, transactions_idx__client_data index has transaction_time_commit as the last column in index. When I 'recreate' the database, and run the queries again, first run which uses sequential scan is around 10 seconds, heavy I/O, any subsequent query run takes cca 3900 msecs, as shown above. When I say 'disable seqscan', planner uses Bitmap Index Scan, as shown above, just that the first query takes around 25 seconds to run, with heavy I/O. Any subsequent query runs take somewhat less than 2 seconds, as shown above. I'm not sure on what to do to minimize the impact of the OS-cache, apart from taking RAM modules out of the machine - if you have any suggestions I'll try to apply them. On production database I changed the index so that 'transaction_time_commit' is the first column, and now I don't get any sequential scans on transactions table when only WHERE condition is on transaction_time_commit. Mike -- 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 not willing to use an index?
Mario Splivalo wrote: Robert Haas wrote: jura=# set enable_seqscan to false; SET jura=# explain analyze select * from transactions where transaction_time_commit between '2008-01-01' and '2008-01-31 23:59:59'; QUERY PLAN --- Bitmap Heap Scan on transactions (cost=428882.89..651630.52 rows=759775 width=91) (actual time=1358.040..1633.867 rows=722176 loops=1) Recheck Cond: ((transaction_time_commit = '2008-01-01 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit = '2008-01-31 23:59:59+01'::timestamp with time zone)) - Bitmap Index Scan on transactions_idx__client_data (cost=0.00..428692.95 rows=759775 width=0) (actual time=1354.485..1354.485 rows=722176 loops=1) Index Cond: ((transaction_time_commit = '2008-01-01 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit = '2008-01-31 23:59:59+01'::timestamp with time zone)) Total runtime: 1778.938 ms (5 rows) I neglected to paste this 'explain analyze', when I changed the index so that 'transaction_time_commit' is first column in the index: jura=# explain analyze select * from transactions where transaction_time_commit between '2009-01-01' and '2009-01-31 23:59:59'; QUERY PLAN --- Bitmap Heap Scan on transactions (cost=7550.51..233419.58 rows=250880 width=91) (actual time=95.139..280.008 rows=525051 loops=1) Recheck Cond: ((transaction_time_commit = '2009-01-01 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit = '2009-01-31 23:59:59+01'::timestamp with time zone)) - Bitmap Index Scan on transactions_idx__client_data (cost=0.00..7487.79 rows=250880 width=0) (actual time=93.382..93.382 rows=525051 loops=1) Index Cond: ((transaction_time_commit = '2009-01-01 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit = '2009-01-31 23:59:59+01'::timestamp with time zone)) Total runtime: 386.665 ms (5 rows) Thank you, Tom! Mike -- 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] scaling up postgres
On Sat, 2006-06-03 at 11:43 +0200, Steinar H. Gunderson wrote: On Sat, Jun 03, 2006 at 10:31:03AM +0100, [EMAIL PROTECTED] wrote: I do have 2 identical beasts (4G - biproc Xeon 3.2 - 2 Gig NIC) One beast will be apache, and the other will be postgres. I'm using httperf/autobench for measurments and the best result I can get is that my system can handle a trafiic of almost 1600 New con/sec. What version of PostgreSQL? (8.1 is better than 8.0 is much better than 7.4.) Have you remembered to turn HT off? Have you considered Opterons instead of Xeons? (The Xeons generally scale bad with PostgreSQL.) What kind of queries Could you point out to some more detailed reading on why Xeons are poorer choice than Opterons when used with PostgreSQL? Mario ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Speedup hint needed, if available? :)
loops=1) - Seq Scan on user_subscription_credits_taken (cost=1.00..100011145.43 rows=747843 width=8) (actual time=0.023..4386.769 rows=747884 loops=1) Total runtime: 56536.774 ms (13 rows) Thank you all in advance, Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Speedup hint needed, if available? :)
On Tue, 2006-05-30 at 11:05 -0400, Tom Lane wrote: Do you have realistic test data? The EXPLAIN shows that this is pulling 275366 of the 826032 rows in the two tables, which seems like rather a lot for a single user. If it's reasonable that the query needs to fetch one-third of the data, then you should resign yourself to it taking awhile :-( I'd say so, yes. The user_subscription table now has only six rows, but the number of actions (giving/taking credits) for a user could easily be as high as 50.000. If the expected number of matching rows were much smaller, it would make sense to use indexscans over the two big tables, but unfortunately existing PG releases don't know how to generate an indexscan join with a UNION ALL in between :-(. FWIW, 8.2 will be able to do it. In current releases the only thing I can suggest is to merge user_subscription_credits_given and user_subscription_credits_taken into one table so you don't need the UNION ALL. See, that's an idea! :) Thnx, I'll try that. Is it inapropriate to ask about rough estimate on availableness of 8.2? :) Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Lot'sa joins - performance tip-up, please?
On Wed, 2006-05-10 at 17:10 -0500, Jim C. Nasby wrote: On Thu, May 04, 2006 at 04:45:57PM +0200, Mario Splivalo wrote: Well, here's the problem... - Nested Loop (cost=0.00..176144.30 rows=57925 width=26) (actual time=1074.984..992536.243 rows=57925 loops=1) - Seq Scan on ticketing_codes_played (cost=0.00..863.25 rows=57925 width=8) (actual time=74.479..2047.993 rows=57925 loops=1) - Index Scan using ticketing_codes_pk on ticketing_codes (cost=0.00..3.01 rows=1 width=18) (actual time=17.044..17.052 rows=1 loops=57925) Index Cond: (ticketing_codes.code_id = outer.code_id) Anyone have any idea why on earth it's doing that instead of a hash or merge join? In any case, try swapping the order of ticketing_codes_played and ticketing_codes. Actually, that'd probably make it worse. I tried that, no luck. The best performance I achieve with creating temporary table. And... Try SET enable_nestloop = off; This helps also. I don't get sequential scans any more. I'd like a tip on how to set 'enable_nestloop = off' trough JDBC? Mario -- I can do it quick, I can do it cheap, I can do it well. Pick any two. Mario Splivalo [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Lot'sa joins - performance tip-up, please?
On Wed, 2006-05-03 at 10:20 -0500, Dave Dutcher wrote: - Nested Loop (cost=0.00..176144.30 rows=57925 width=26) (actual time=68.322..529472.026 rows=57925 loops=1) - Seq Scan on ticketing_codes_played (cost=0.00..863.25 rows=57925 width=8) (actual time=0.042..473.881 rows=57925 loops=1) - Index Scan using ticketing_codes_pk on ticketing_codes (cost=0.00..3.01 rows=1 width=18) (actual time=9.102..9.108 rows=1 loops=57925) Index Cond: (ticketing_codes.code_id = outer.code_id) Total runtime: 542000.093 ms (27 rows) I'll be more than happy to provide any additional information that I may be able to gather. I'd be most happy if someone would scream something like four joins, smells like a poor design because design is poor, but the system is in production, and I have to bare with it. It looks like that nested loop which is joining ticketing_codes_played to ticketing_codes is the slow part. I'm curious how many rows are in the ticketing_codes table? Four or five joins does not seem like a lot to me, but it can be slow if you are joining big tables with other big tables. Ticketing_codes table has 1100 records, and it's expected to grow. I tried playing with JOIN order as Tom suggested, but performance is the same. Mario ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Lot'sa joins - performance tip-up, please?
On Wed, 2006-05-03 at 13:58 -0400, Tom Lane wrote: Mario Splivalo [EMAIL PROTECTED] writes: I have a quite large query that takes over a minute to run on my laptop. The EXPLAIN output you provided doesn't seem to agree with the stated query. Where'd the service_id = 1102 condition come from? I guess I copypasted the additional WHERE to te EXPLAIN ANALYZE query. This is the correct one, without the WHERE: Hash Left Join (cost=198628.35..202770.61 rows=121 width=264) (actual time=998008.264..999645.322 rows=5706 loops=1) Hash Cond: (outer.message_id = inner.message_id) - Merge Left Join (cost=21943.23..21950.96 rows=121 width=238) (actual time=4375.510..4540.772 rows=5706 loops=1) Merge Cond: (outer.message_id = inner.message_id) - Sort (cost=21847.62..21847.92 rows=121 width=230) (actual time=3304.787..3378.515 rows=5706 loops=1) Sort Key: messages.id - Hash Join (cost=20250.16..21843.43 rows=121 width=230) (actual time=1617.370..3102.470 rows=5706 loops=1) Hash Cond: (outer.message_id = inner.id) - Seq Scan on ticketing_messages (cost=0.00..1212.37 rows=75937 width=14) (actual time=10.554..609.967 rows=75937 loops=1) - Hash (cost=20244.19..20244.19 rows=2391 width=216) (actual time=1572.889..1572.889 rows=5706 loops=1) - Nested Loop (cost=1519.21..20244.19 rows=2391 width=216) (actual time=385.582..1449.207 rows=5706 loops=1) - Seq Scan on services (cost=0.00..4.20 rows=3 width=54) (actual time=20.829..20.859 rows=2 loops=1) Filter: (type_id = 10) - Bitmap Heap Scan on messages (cost=1519.21..6726.74 rows=1594 width=162) (actual time=182.346..678.800 rows=2853 loops=2) Recheck Cond: ((outer.id = messages.service_id) AND (messages.receiving_time = '2006-02-12 00:00:00+01'::timestamp with time zone) AND (messages.receiving_time = '2006-03-18 23:00:00+01'::timestamp with time zone)) - BitmapAnd (cost=1519.21..1519.21 rows=1594 width=0) (actual time=164.311..164.311 rows=0 loops=2) - Bitmap Index Scan on idx_service_id (cost=0.00..84.10 rows=14599 width=0) (actual time=66.809..66.809 rows=37968 loops=2) Index Cond: (outer.id = messages.service_id) - Bitmap Index Scan on idx_messages_receiving_time (cost=0.00..1434.87 rows=164144 width=0) (actual time=192.633..192.633 rows=184741 loops=1) Index Cond: ((receiving_time = '2006-02-12 00:00:00+01'::timestamp with time zone) AND (receiving_time = '2006-03-18 23:00:00+01'::timestamp with time zone)) - Sort (cost=95.62..99.17 rows=1421 width=8) (actual time=1070.678..1072.999 rows=482 loops=1) Sort Key: ticketing_winners.message_id - Seq Scan on ticketing_winners (cost=0.00..21.21 rows=1421 width=8) (actual time=424.836..1061.834 rows=1421 loops=1) - Hash (cost=176144.30..176144.30 rows=57925 width=26) (actual time=993592.980..993592.980 rows=57925 loops=1) - Nested Loop (cost=0.00..176144.30 rows=57925 width=26) (actual time=1074.984..992536.243 rows=57925 loops=1) - Seq Scan on ticketing_codes_played (cost=0.00..863.25 rows=57925 width=8) (actual time=74.479..2047.993 rows=57925 loops=1) - Index Scan using ticketing_codes_pk on ticketing_codes (cost=0.00..3.01 rows=1 width=18) (actual time=17.044..17.052 rows=1 loops=57925) Index Cond: (ticketing_codes.code_id = outer.code_id) Total runtime: 999778.981 ms In general, I'd suggest playing around with the join order. Existing releases of PG tend to throw up their hands when faced with a mixture of outer joins and regular joins, and just join the tables in the order listed. 8.2 will be smarter about this, but for now you have to do it by hand ... No luck for me there. But, I found out that if I first do join on ticketing_codes and ticketing_codes_played, put the result to temporary table, and then join that temporary table with the rest of the query (the SELECT that is in parenthesis is transfered to a temporary table) the query is almost twice as fast. As mentioned before, ticketing_codes has 1100 records. Mario P.S. Is it just me, or posting to psql-perofrmance is laged, quite a bit? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Lot'sa joins - performance tip-up, please?
I have a quite large query that takes over a minute to run on my laptop. On the db server it takes olmost 20 seconds, but I have 200+ concurent users who will be running similair querries, and during the query the I/O goes bezerk, I read 30MB/s reading (iostat tells so). So, before going into denormalization, I wonder if I could do something to speed things up. The query is like this: select * from messages join services on services.id = messages.service_id join ticketing_messages on messages.id = ticketing_messages.message_id left join ticketing_winners on ticketing_winners.message_id = ticketing_messages.message_id left join ( select * from ticketing_codes_played join ticketing_codes on ticketing_codes.code_id = ticketing_codes_played.code_id ) as codes on codes.message_id = ticketing_messages.message_id where services.type_id = 10 and messages.receiving_time between '2006-02-12' and '2006-03-18 23:00:00'; The explain analyze of the above produces this: QUERY PLAN - Merge Left Join (cost=221692.04..222029.29 rows=3772 width=264) (actual time=539169.163..541579.504 rows=75937 loops=1) Merge Cond: (outer.message_id = inner.message_id) - Sort (cost=40080.17..40089.60 rows=3772 width=238) (actual time=8839.072..9723.371 rows=75937 loops=1) Sort Key: messages.id - Hash Left Join (cost=2259.40..39856.10 rows=3772 width=238) (actual time=1457.451..7870.830 rows=75937 loops=1) Hash Cond: (outer.message_id = inner.message_id) - Nested Loop (cost=2234.64..39811.76 rows=3772 width=230) (actual time=1418.911..7063.299 rows=75937 loops=1) - Index Scan using pk_services on services (cost=0.00..4.46 rows=1 width=54) (actual time=28.261..28.271 rows=1 loops=1) Index Cond: (1102 = id) Filter: (type_id = 10) - Hash Join (cost=2234.64..39769.58 rows=3772 width=176) (actual time=1390.621..6297.501 rows=75937 loops=1) Hash Cond: (outer.id = inner.message_id) - Bitmap Heap Scan on messages (cost=424.43..32909.53 rows=74408 width=162) (actual time=159.796..4329.125 rows=75937 loops=1) Recheck Cond: (service_id = 1102) - Bitmap Index Scan on idx_service_id (cost=0.00..424.43 rows=74408 width=0) (actual time=95.197..95.197 rows=75937 loops=1) Index Cond: (service_id = 1102) - Hash (cost=1212.37..1212.37 rows=75937 width=14) (actual time=940.372..940.372 rows=75937 loops=1) - Seq Scan on ticketing_messages (cost=0.00..1212.37 rows=75937 width=14) (actual time=12.122..461.960 rows=75937 loops=1) - Hash (cost=21.21..21.21 rows=1421 width=8) (actual time=38.496..38.496 rows=1421 loops=1) - Seq Scan on ticketing_winners (cost=0.00..21.21 rows=1421 width=8) (actual time=24.534..31.347 rows=1421 loops=1) - Sort (cost=181611.87..181756.68 rows=57925 width=26) (actual time=530330.060..530647.055 rows=57925 loops=1) Sort Key: ticketing_codes_played.message_id - Nested Loop (cost=0.00..176144.30 rows=57925 width=26) (actual time=68.322..529472.026 rows=57925 loops=1) - Seq Scan on ticketing_codes_played (cost=0.00..863.25 rows=57925 width=8) (actual time=0.042..473.881 rows=57925 loops=1) - Index Scan using ticketing_codes_pk on ticketing_codes (cost=0.00..3.01 rows=1 width=18) (actual time=9.102..9.108 rows=1 loops=57925) Index Cond: (ticketing_codes.code_id = outer.code_id) Total runtime: 542000.093 ms (27 rows) I'll be more than happy to provide any additional information that I may be able to gather. I'd be most happy if someone would scream something like four joins, smells like a poor design because design is poor, but the system is in production, and I have to bare with it. Mario -- I can do it quick, I can do it cheap, I can do it well. Pick any two. Mario Splivalo [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Identical query on two machines, different plans....
I have copied the database from production server to my laptop (pg_dump,
etc...) to do some testing.
While testing I have found out that one particular query is beeing much
slower on my machine than on the server (it's not just because my laptop
is much slower than the server), and found out that postgres is using
different plan on server than on my laptop. Both on server and on my
laptop is postgres-8.1.2, running on Debian (sarge on server, Ubuntu on
my laptop), with 2.6 kernel, I compiled postgres with gcc4 on both
machines.
The query is like this:
on the server:
pulitzer2=# explain analyze select code_id from ticketing_codes where
code_group_id = 1000 and code_value = UPPER('C7ZP2U');
QUERY PLAN
---
Index Scan using ticketing_codes_uq_value_group_id on ticketing_codes
(cost=0.00..6.02 rows=1 width=4) (actual time=0.104..0.107 rows=1
loops=1)
Index Cond: (((code_value)::text = 'C7ZP2U'::text) AND (code_group_id
= 1000))
Total runtime: 0.148 ms
(3 rows)
And, on my laptop:
som_pulitzer2=# explain analyze select code_id from ticketing_codes
where code_group_id = 1000 and code_value = UPPER('C7ZP2U');
QUERY
PLAN
--
Bitmap Heap Scan on ticketing_codes (cost=2.01..1102.05 rows=288
width=4) (actual time=88.164..88.170 rows=1 loops=1)
Recheck Cond: (((code_value)::text = 'C7ZP2U'::text) AND
(code_group_id = 1000))
- Bitmap Index Scan on ticketing_codes_uq_value_group_id
(cost=0.00..2.01 rows=288 width=0) (actual time=54.397..54.397 rows=1
loops=1)
Index Cond: (((code_value)::text = 'C7ZP2U'::text) AND
(code_group_id = 1000))
Total runtime: 88.256 ms
(5 rows)
This is the table ticketing_codes:
som_pulitzer2=# \d ticketing_codes;
Table public.ticketing_codes
Column | Type |
Modifiers
---+---+---
code_id | integer | not null default
nextval('ticketing_codes_code_id_seq'::regclass)
code_value| character varying(10) | not null
code_group_id | integer | not null
Indexes:
ticketing_codes_pk PRIMARY KEY, btree (code_id)
ticketing_codes_uq_value_group_id UNIQUE, btree (code_value,
code_group_id)
Foreign-key constraints:
ticketing_codes_fk__ticketing_code_groups FOREIGN KEY
(code_group_id) REFERENCES ticketing_code_groups(group_id)
And the \d command produces the same result on both my server and
laptop.
That query is beeing called from within function, the code is like this:
codeId := code_id from ticketing_codes where code_group_id = 1000 and
code_value = UPPER('C7ZP2U');
codeId has been declared as int4. When that query is run inside the
function, it takes around 20 seconds (compared to 88 miliseconds when I
call it from psql). The query is that very same query, just the values
1000 and 'C7ZP2U' are parametars for the function.
So, the second question would be why is that query much much slower when
run from within function? Is there a way to see an execution plan for
the query inside the function?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
I can do it quick, I can do it cheap, I can do it well. Pick any two.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] Identical query on two machines, different plans....
On Thu, 2006-04-20 at 15:59 +0200, Csaba Nagy wrote: You very likely forgot to run ANALYZE on your laptop after copying the data. Observe the different row count estimates in the 2 plans... HTH, Csaba. Sometimes I wish I am Dumbo the Elephant, so I could cover myself with me ears... Thnx :) Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] SELECT FOR UPDATE performance is bad
On Tue, 2006-04-18 at 11:33 -0400, Tom Lane wrote: Mario Splivalo [EMAIL PROTECTED] writes: If there is concurrent locking, you're also running a big risk of deadlock because two processes might try to lock the same rows in different orders. I think there is no risk of a deadlock, since that particular function is called from the middleware (functions are used as interface to the database), and the lock order is always the same. No, you don't even know what the order is, let alone that it's always the same. You got me confused here! :) If I have just only one function that acts as a interface to the middleware, and all the operations on the database are done trough that one function, and I carefuly design that function so that I first grab the lock, and then do the stuff, aint I pretty sure that I won't be having any deadlocks? Now, I just need to have serialization, I need to have clients 'line up' in order to perform something in the database. Actually, users are sending codes from the newspaper, beer-cans, Cola-cans, and stuff, and database needs to check has the code allready been played. Since the system is designed so that it could run multiple code-games (and then there similair code could exists for coke-game and beer-game), I'm using messages table to see what code-game (i.e. service) that particular code belongs. I'd suggest using a table that has exactly one row per code-game, and doing a SELECT FOR UPDATE on that row to establish the lock you need. This need not have anything to do with the tables/rows you are actually intending to update --- although obviously such a convention is pretty fragile if you have updates coming from a variety of code. I think it's reasonably safe when you're funneling all the operations through a bit of middleware. I tend to design my applications so I don't have flying SQL in my java/python/c#/php/whereever code, all the database stuff is done trough the functions which are designed as interfaces. Those functions are also designed so they don't stop each other. So, since I need the serialization, I'll do as you suggested, using a lock-table with exactley one row per code-game. Just one more question here, it has to do with postgres internals, but still I'd like to know why is postgres doing such huge i/o (in my log file I see a lot of messages that say LOG: archived transaction log file when performing that big FOR UPDATE. Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] SELECT FOR UPDATE performance is bad
For the purpose of the application I need to establish some form of serialization, therefore I use FOR UPDATE. The query, inside the function, is like this: pulitzer2=# explain analyze select id FROM messages JOIN ticketing_codes_played ON id = message_id WHERE service_id = 1102 AND receiving_time BETWEEN '2006-03-01' AND '2006-06-30' FOR UPDATE; QUERY PLAN - Hash Join (cost=32131.04..34281.86 rows=627 width=16) (actual time=742.806..1491.864 rows=58005 loops=1) Hash Cond: (outer.message_id = inner.id) - Seq Scan on ticketing_codes_played (cost=0.00..857.17 rows=57217 width=10) (actual time=0.024..209.331 rows=58005 loops=1) - Hash (cost=32090.60..32090.60 rows=16177 width=10) (actual time=742.601..742.601 rows=65596 loops=1) - Bitmap Heap Scan on messages (cost=4153.51..32090.60 rows=16177 width=10) (actual time=160.555..489.459 rows=65596 loops=1) Recheck Cond: ((service_id = 1102) AND (receiving_time = '2006-03-01 00:00:00+01'::timestamp with time zone) AND (receiving_time = '2006-06-30 00:00:00+02'::timestamp with time zone)) - BitmapAnd (cost=4153.51..4153.51 rows=16177 width=0) (actual time=156.900..156.900 rows=0 loops=1) - Bitmap Index Scan on idx_service_id (cost=0.00..469.31 rows=68945 width=0) (actual time=16.661..16.661 rows=66492 loops=1) Index Cond: (service_id = 1102) - Bitmap Index Scan on idx_messages_receiving_time (cost=0.00..3683.95 rows=346659 width=0) (actual time=137.526..137.526 rows=360754 loops=1) Index Cond: ((receiving_time = '2006-03-01 00:00:00+01'::timestamp with time zone) AND (receiving_time = '2006-06-30 00:00:00+02'::timestamp with time zone)) Total runtime: 6401.954 ms (12 rows) Now, this query takes between 8 and 30 seconds, wich is a lot, since during the day we have almost 20 requests per minute. I notice that during the execution of the above mentioned query i/o goes bezerk, iostat tells me that load is around 60%. I tried playing with WAL configuration parametars, even put the log on separate disk spindles, it did nothing. Shall I reconsider the need for the exact lock I developed, or there is something more I could do to speed the things up? Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Measuring the execution time of functions within functions...
I have asked this before, but haven't noticed any response, so if there were any, I appologize for asking this again... I have a function that is called by middle-tier (java trough JDBC), and in postgres log I can see only the execution time of that function. I have no idea how long are functions insde taking time to execute. Since the function is written in plpgsql I tried to calculate the durations by using now() function, but realized that within the transaction now() always retunrs the same value. The good thing is that those RAISE NOTICE calls from within my function are logged as they're encountered, so, with carefully placed RAISE NOTICE calls I could see how much time are the -inside- functions taking. For instance: CREATE FUNCTION test_outer() RETURNS void AS $$BODY$$BEGIN RAISE NOTICE 'We start here' PERFORM SELECT someInternalFunction1(); RAISE NOTICE 'InternalFunction1 is done now.'; PERFORM SELECT someInternalFunction2(); RAISE NOTICE 'InternalFunction2 is done now.'; -- ... more code here END$$BODY$$ LANGUAGE 'plpgsql' Is there any other, maybe more convinient way to measure the 'inside' function performance? I also have a problem if the outer function is written in SQL, like this, for instance: CREATE FUNCTION getSomeData(param1, param2, param3) RETURN SETOF someType AS $$BODY$$SELECT * FROM someTable JOIN someOtherFunction($1, $2, $3) ON someTable.col = someOtherFunction.col WHERE someCondition $$BODY$$ LANGUAGE 'sql'. Thank you in advance, Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
