Re: [PERFORM] Performance advice
[ This has been written offline yesterday. Now I see that most of it has already been covered. I send it anyway ... ] On Tue, 24 Jun 2003 09:39:32 +0200, "Michael Mattox" <[EMAIL PROTECTED]> wrote: >Websites are monitored every 5 or 10 minutes (depends on client), >there are 900 monitors which comes out to 7,800 monitorings per hour. So your server load - at least INSERT, UPDATE, DELETE - is absolutely predictable. This is good. It enables you to design a cron-driven VACUUM strategy. |INFO: --Relation public.jdo_sequencex-- |INFO: Pages 28: Changed 1, Empty 0; Tup 1: Vac 5124, Keep 0, UnUsed 0. ^ This table could stand more frequent VACUUMs, every 15 minutes or so. BTW, from the name of this table and from the fact that there is only one live tuple I guess that you are using it to keep track of a sequence number. By using a real sequence you could get what you need with less contention; and you don't have to VACUUM a sequence. |INFO: --Relation public.monitorx-- |INFO: Removed 170055 tuples in 6036 pages. |CPU 0.52s/0.81u sec elapsed 206.26 sec. |INFO: Pages 6076: Changed 0, Empty 0; Tup 2057: Vac 170055, Keep 568, UnUsed 356. |Total CPU 6.28s/13.23u sec elapsed 486.07 sec. The Vac : Tup ratio for this table is more than 80. You have to VACUUM this table more often. How long is "overnight"? Divide this by 80 and use the result as the interval between VACUUM [VERBOSE] [ANALYSE] public.monitorx; Thus you'd have approximately as many dead tuples as live tuples and the table size should not grow far beyond 150 pages (after an initial VACUUM FULL, of course). Then VACUUM of this table should take no more than 20 seconds. Caveat: Frequent ANALYSEs might trigger the need to VACUUM pg_catalog.pg_statistic. > The >monitor table has columns "nextdate" and "status" which are updated with >every monitoring, [...] > updating the "nextdate" before the monitoring and inserting the >status and status item records after. Do you mean updating monitor.nextdate before the monitoring and monitor.status after the monitoring? Can you combine these two UPDATEs into one? > During the vacuum my application does slow down quite a bit Yes, because VACUUM does lots of I/O. > and afterwards is slow speeds back up. ... because the working set is slowly fetched into the cache after having been flushed out by VACUUM. Your five largest relations are monitorstatus_statusitemsx, monitorstatusitemlistd8ea58a5x, monitorstatusitemlistx, monitorstatusitemx, and monitorstatusx. The heap relations alone (without indexes) account for 468701 pages, almost 4GB. VACUUMing these five relations takes 23 minutes for freeing less than 200 out of 6 million tuples for each relation. This isn't worth it. Unless always the same tuples are updated over and over, scheduling a VACUUM for half a million deletions/updates should be sufficient. >shared_buffers = 3072 # min max_connections*2 or 16, 8KB each >sort_mem = 8192 # min 64, size in KB >vacuum_mem = 24576 # min 1024, size in KB > >The rest are left uncommented (using the defaults). As has already been said, don't forget effective_cache_size. I'm not so sure about random_page_cost. Try to find out which queries are too slow. EXPLAIN ANALYSE is your friend. One more thing: I see 2 or 3 UPDATEs and 5 INSERTs per monitoring. Are these changes wrapped into a single transaction? Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Performance advice
On Wed, 25 Jun 2003 11:47:48 +0200, "Michael Mattox" <[EMAIL PROTECTED]> wrote: >> |INFO: --Relation public.jdo_sequencex-- >> |INFO: Pages 28: Changed 1, Empty 0; Tup 1: Vac 5124, Keep 0, UnUsed 0. >> ^ >> This table could stand more frequent VACUUMs, every 15 minutes or so. > >Can you explain what the "Vac" is That's a long story, where shall I start? Search for MVCC in the docs and in the list archives. So you know that every DELETE and every UPDATE leaves behind old versions of tuples. The space occupied by these cannot be used immediately. VACUUM is responsible for finding dead tuples, which are so old that there is no active transaction that could be interested in their contents, and reclaiming the space. The number of such tuples is reported as "Vac". > and how you knew that it should be vacuumed more often? jdo_sequencex stores (5000 old versions and 1 active version of) a single row in 28 pages. Depending on when you did ANALYSE it and depending on the SQL statement, the planner might think that a sequential scan is the most efficient way to access this single row. A seq scan has to read 28 pages instead of a single page. Well, probably all 28 pages are in the OS cache or even in PG's shared buffers, but 27 pages are just wasted and push out pages you could make better use of. And processing those 28 pages does not come at no CPU cost. If you VACUUM frequently enough, this relation never grows beyond one page. >I'm using Java Data Objects (JDO) which is an O/R mapper. It generated the >schema from my object model by default it used a table for a sequence. I >just got finished configuring it to use a real postgres sequence. With the >way they have it designed, it opens and closes a connection each time it >retrieves a sequence. Would I get a performance increase if I modify their >code to retrieve multiple sequence numbers in one connection? For example I >could have it grab 50 at a time, which would replace 50 connections with 1. Better yet you modify the code to use the normal access functions for sequences. Servus Manfred ---(end of broadcast)--- TIP 3: 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] problem with pg_statistics
On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >Andre Schubert <[EMAIL PROTECTED]> writes: >> i think i need a little help with a problem with pg_statistic. > >Try reducing random_page_cost With index scan cost being more than 25 * seq scan cost, I guess that - all other things held equal - even random_page_cost = 1 wouldn't help. Andre might also want to experiment with effective_cache_size and with ALTER TABLE ... SET STATISTICS. Or there's something wrong with correlation? Andre, what hardware is this running on? What are the values of shared_buffers, random_page_cost, effective_cache_size, ... ? Could you show us the result of SELECT * FROM pg_stats WHERE tablename = "tbl_traffic" AND attname = "time_stamp"; Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] problem with pg_statistics
On Fri, 27 Jun 2003 08:07:35 +0200, Andre Schubert <[EMAIL PROTECTED]> wrote: >Traffic data are inserted every 5 minutes with the actual datetime >of the transaction, thatswhy the table should be physically order by time_stamp. So I'd expect a correlation of nearly 1. Why do your statistics show a value of -0.479749? A negative correlation is a sign of descending sort order, and correlation values closer to 0 indicate poor correspondence between column values and tuple positions. Could this be the effect of initial data loading? Are there any updates or deletions in your traffic table? >To answer Manfreds questions: >> Andre, what hardware is this running on? What are the values of >> shared_buffers, random_page_cost, effective_cache_size, ... ? Could >> you show us the result of >> >> SELECT * FROM pg_stats >> WHERE tablename = "tbl_traffic" AND attname = "time_stamp"; ^ ^ ^ ^ Oops, these should have been single quotes. It's too hot here these days :-) >sort_mem = 32000 >shared_buffers = 13000 Personally I would set them to lower values, but if you have good reasons ... >#effective_cache_size = 1000 # default in 8k pages This is definitely too low. With 512MB or more I tend to set this to ca. 80% of available RAM. Use top and free to find hints for good values. Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] problem with pg_statistics
On Fri, 27 Jun 2003 11:10:58 +0200, Andre Schubert <[EMAIL PROTECTED]> wrote: >Once a month we delete the all data of the oldest month. >And after that a vacuum full verbose analyze is performed. >Could this cause reordering of the data ? I may be wrong, but I think VACUUM FULL starts taking tuples from the end of the relation and puts them into pages at the beginning until read and write position meet somewhere in the middle. This explains the bad correlation. >And should i do a cluster idx_ts tbl_traffic ? I think so. >> >#effective_cache_size = 1000 # default in 8k pages >> >> This is definitely too low. With 512MB or more I tend to set this to >> ca. 80% of available RAM. Use top and free to find hints for good >> values. >> > >Ok, i will talk with my coworker ( he is the sysadmin of our machine ) >and look if can use such amount of RAM, because there are several other >processes that are running on these machines. >But i will test and report ... effective_cache_size does not *control* resource consumption, it just *reports* it as a hint to the planner. Servus Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Effective Cache Size
On Tue, 1 Jul 2003 15:50:14 +0200 , Howard Oblowitz <[EMAIL PROTECTED]> wrote: >What then will be the effect of setting this too high? The planner might choose an index scan where a sequential scan would be faster. >And too low? The planner might choose a sequential scan where an index scan would be faster. >How does it impact on other applications eg Java ? It doesn't -- at least not directly. (There could be very subtle effects when Postgres does a sequential scan over a large relation thus pushing everything else out of the cache, where an index scan would have read only a small number of pages. Or when a large index scan turns your machine from CPU bound to I/O bound.) Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Version 7 question
On Tue, 1 Jul 2003 15:02:21 +0200, "Michael Mattox" <[EMAIL PROTECTED]> wrote: >I have 1.5 gigs of RAM on my >server but I'm also running a few other java programs that take up probably >500 megs total of memory, leaving me 1gig for Postgres. Should I set my >shared buffers to be 25% of 1gig? That would be 32768. Then what should my >effective cache be? Right now I have it set to 64000 which would be >512megs. Between the buffers and cache that'd be a total of 768megs, >leaving approximately 768 for my other java apps & the OS. Michael, by setting effective_cache_size you do not allocate anything. This configuration variable is just a *hint* to the planner how much RAM is used for caching on your system (as shown by top or free). Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Help on my database performance
On Thu, 31 Jul 2003 11:06:09 -0400, "Jianshuo Niu" <[EMAIL PROTECTED]> wrote: >I ran the same explain analyze on two similar tables. However, the table >with less data took much more time than the one with more data. Could anyone >tell me what happened? >Seq Scan on tfd_catalog (cost=0.00..43769.82 rows=161282 width=10) (actual >time=3928.64..12905.76 rows=161282 loops=1) >Total runtime: 13240.21 msec > >Seq Scan on hm_catalog (cost=0.00..22181.18 rows=277518 width=9) (actual >time=21.32..6420.76 rows=277518 loops=1) >Total runtime: 6772.95 msec The first SELECT takes almost twice the time because tfd_catalog has almost twice as many pages than hm_catalog. This may be due to having wider tuples or more dead tuples in tfd_catalog. In the former case theres not much you can do. But the high startup cost of the first SELECT is a hint for lots of dead tuples. So VACUUM FULL ANALYSE might help. Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
[jumping in late due to vacation] On Thu, 3 Jul 2003 17:06:46 -0700, Sean Chittenden <[EMAIL PROTECTED]> wrote: >> is some other problem that needs to be solved. (I'd wonder about >> index correlation myself; we know that that equation is pretty >> bogus.) > >Could be. I had him create a multi-column index on the date and a >non-unique highly redundant id. Tom has already suspected index correlation to be a possible source of the problem and recommended to CLUSTER on the index. A weakness of the current planner implementation is that a multi column index is always thought to have low correlation. In your case even after CLUSTER the 2-column index on (date, sensorid) is treated like a single column index with correlation 0.5. I have an experimental patch lying around somewhere that tries to work around these problems by offering different estimation methods for index scans. If you are interested, I'll dig it out. In the meantime have him try with a single column index on date. On 04 Jul 2003 08:29:04 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote: |That's one heck of a poor estimate for the number of rows returned. | |> -> Seq Scan on mss_fwevent (cost=0.00..223312.60 rows=168478 width=12) |(actual time=24253.66..24319.87 rows=320 loops=1) > -> Index Scan using mss_fwevent_evtime_sensorid_idx on mss_fwevent > (cost=0.00..2442524.70 rows=168478 width=12) >(actual time=68.36..132.84 rows=320 loops=1) > Index Cond: ((evtime > (now() - '06:00'::interval)) AND (sensorid = 7)) > Filter: (NOT "action") Estimated number of rows being wrong by a factor 500 seems to be the main problem hiding everything else. With statistics already set to 1000, does this mean that sensorid, evtime, and action are not independent? It'd be interesting to know whether the estimation error comes from "Index Cond" or from "Filter". Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Help on my database performance
On Thu, 31 Jul 2003 16:08:11 -0400, "Jianshuo Niu" <[EMAIL PROTECTED]> wrote: >explain analyze select count(*) from tfd_catalog ; >NOTICE: QUERY PLAN: > >Aggregate (cost=15986.02..15986.02 rows=1 width=0) > (actual time=1089.99..1089.99 rows=1 loops=1) > -> Seq Scan on tfd_catalog (cost=0.00..15582.82 rows=161282 width=0) > (actual time=0.11..833.41 rows=161282 loops=1) >Total runtime: 1090.51 msec >Could you tell me what does "Aggregate (cost=15986.02..15986.02 rows=1 >width=0) (actual time=1089.99..1089.99 rows=1 loops=1)" mean? It does not >show in my previous report. In your first post you did SELECT productid FROM tfd_catalog; now you did SELECT count(*) FROM tfd_catalog; count() is an aggregate function which in your case takes 161282 rows as input and produces a single row as output. The "actual" part of the "Aggregate" line tells you that the first resulting row is generated 1089.99 milliseconds after query start and the last row (not surprisingly) at the same time. The "cost" part contains the planner's estimations for these values. Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec
On Fri, 1 Aug 2003 18:17:17 -0300, "Fernando Papa" <[EMAIL PROTECTED]> wrote: > AND cont_publicacion.fecha_publicacion = (SELECT >max(cp1.fecha_publicacion) > FROM cont_publicacion cp1 > WHERE cp1.id_instalacion = >cont_publicacion.id_instalacion >AND cp1.id_contenido = cont_publicacion.id_contenido > >AND cp1.generar_vainilla = >cont_publicacion.generar_vainilla) If certain uniqueness conditions are met, the Postgres specific DISTINCT ON clause could help totally eliminating the subselect: SELECT DISTINCT ON ( cp.id_instalacion, cp.id_contenido, cp.generar_vainilla, cp.fecha_publicacion ) cc.id_contenido ,cc.pertenece_premium ,cc.Titulo_esp as v_sufix ,cc.url_contenido ,cc.tipo_acceso ,cc.id_sbc ,cc.cant_vistos ,cc.cant_votos ,cc.puntaje_total ,cc.id_contenido_padre ,jc.imagen_tapa_especial ,jc.info_general_esp as info_general ,jc.ayuda ,jc.tips_tricks_esp as tips_tricks ,jc.mod_imagen_tapa_especial ,cp.fecha_publicacion as fecha_publicacion ,cp.generar_Vainilla FROM cont_contenido cc ,juegos_config jc ,cont_publicacion cp WHERE cc.id_instalacion= 2 AND cc.id_sbc = 619 AND cc.id_tipo = 2 AND cc.id_instalacion = jc.id_instalacion AND cc.id_contenido = jc.id_contenido AND upper(cp.generar_Vainilla) = 'S' AND cp.id_instalacion = cc.id_instalacion AND cp.id_contenido = cc.id_contenido ORDER BY cp.id_instalacion, cp.id_contenido, cp.generar_vainilla, cp.fecha_publicacion desc However, this doesn't get the result in the original order, so you have to wrap another SELECT ... ORDER BY ... LIMIT around it. Or try to move the subselect into the FROM clause: SELECT cc.id_contenido ,cc.pertenece_premium ,cc.Titulo_esp as v_sufix ,cc.url_contenido ,cc.tipo_acceso ,cc.id_sbc ,cc.cant_vistos ,cc.cant_votos ,cc.puntaje_total ,cc.id_contenido_padre ,jc.imagen_tapa_especial ,jc.info_general_esp as info_general ,jc.ayuda ,jc.tips_tricks_esp as tips_tricks ,jc.mod_imagen_tapa_especial ,cp.fecha_publicacion as fecha_publicacion ,cp.generar_Vainilla FROM cont_contenido cc ,juegos_config jc ,(SELECT DISTINCT ON ( id_instalacion, id_contenido, generar_vainilla, fecha_publicacion ) * FROM cont_publicacion ORDER BY id_instalacion, id_contenido, generar_vainilla, fecha_publicacion desc ) AS cp WHERE cc.id_instalacion= 2 AND cc.id_sbc = 619 AND cc.id_tipo = 2 AND cc.id_instalacion = jc.id_instalacion AND cc.id_contenido = jc.id_contenido AND upper(cp.generar_Vainilla) = 'S' AND cp.id_instalacion = cc.id_instalacion AND cp.id_contenido = cc.id_contenido ORDER BY cp.fecha_publicacion desc LIMIT 10 OFFSET 0 [completely untested] Servus Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec
On Mon, 4 Aug 2003 12:02:46 -0300, "Fernando Papa" <[EMAIL PROTECTED]> wrote: >FROM >cont_contenido >,juegos_config >,cont_publicacion >,(SELECT max(cp1.fecha_publicacion) as max_pub > --change here > FROM cont_publicacion cp1) a --change here But this calculates the global maximum, not per id_instalacion, id_contenido, and generar_vainilla as in >AND cont_publicacion.fecha_publicacion = (SELECT > max(cp1.fecha_publicacion) > FROM cont_publicacion cp1 > WHERE cp1.id_instalacion = > cont_publicacion.id_instalacion > AND cp1.id_contenido = > cont_publicacion.id_contenido > AND cp1.generar_vainilla = > cont_publicacion.generar_vainilla) Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] OSDL Database Test Suite 3 is available on PostgreSQL
On 01 Aug 2003 11:04:10 -0700, Jenny Zhang <[EMAIL PROTECTED]> wrote: >A sample OSDL-DBT3 test result report can be found at: >http://khack.osdl.org/stp/276912/ > >Your comments are welcome, | effective_cache_size | 1000 With 4GB of memory this is definitely too low and *can* (note that I don't say *must*) lead the planner to wrong decisions. | shared_buffers | 15200 ... looks reasonable. Did you test with other values? | sort_mem | 524288 This is a bit high, IMHO, but might be ok given that DBT3 is not run with many concurrent sessions (right?). http://khack.osdl.org/stp/276912/results/plot/vmstat_swap.png shows some swapping activity towards the end of the run which could be caused by a too high sort_mem setting. Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec
On Mon, 04 Aug 2003 16:10:18 +0200, I wrote: >SELECT DISTINCT ON ( >cp.id_instalacion, >cp.id_contenido, >cp.generar_vainilla, >cp.fecha_publicacion > ) Cut'n'paste error! fecha_publicacion should not be in the DISTINCT ON list. The same error is in my second suggestion (FROM (subselect)). Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Index correlation (was: Moving postgresql.conf tunables into 2003... )
On Thu, 07 Aug 2003 19:31:52 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: >The correlation is between index order and heap order --- that is, are >the tuples in the table physically in the same order as the index? >The better the correlation, the fewer heap-page reads it will take to do >an index scan. This is true for a column that is the first column of a btree index. Correlation doesn't help with additional index columns and with functional indices. >Note it is possible to measure correlation without regard to whether >there actually is any index; But there is no need to, because the correlation is only used for index access cost estimation. >One problem we have is extrapolating from the single-column correlation >stats computed by ANALYZE to appropriate info for multi-column indexes. >It might be that the only reasonable fix for this is for ANALYZE to >compute multi-column stats too when multi-column indexes are present. I wonder whether it would be better to drop column correlation and calculate index correlation instead, i.e. correlation of index tuples to heap tuple positions. This would solve both the multi-column index and the functional index cost estimation problem. >People are used to the assumption that you don't need to re-ANALYZE >after creating a new index, but maybe we'll have to give that up. Index correlation would be computed on CREATE INDEX and whenever the heap relation is analysed ... Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
On Tue, 5 Aug 2003 15:26:09 -0700, Sean Chittenden <[EMAIL PROTECTED]> wrote: >> I have an experimental patch lying around somewhere that tries to >> work around these problems by offering different estimation methods >> for index scans. If you are interested, I'll dig it out. > >Sure, I'll take a gander... had my head in enough Knuth recently to >even hopefully have some kind of a useful response to the patch. Sean, the patch is at http://www.pivot.at/pg/16-correlation-732.diff. A short description of its usage can be found at http://archives.postgresql.org/pgsql-performance/2002-11/msg00256.php. If you are interested how the different interpolation methods work, read the source - it shouldn't be too hard to find. You might also want to read the thread starting at http://archives.postgresql.org/pgsql-hackers/2002-10/msg00072.php. >> does this mean that sensorid, evtime, and action are not >> independent? > >Hrm... sensorid is sequence and grows proportional with evtime, >obviously. So a *low* sensorid (7) is quite uncommon for a *late* evtime? This would help understand the problem. Unfortunately I have no clue what to do about it. :-( >Having spent a fair amount of time looking at the two following plans, >it seems as though an additional statistic is needed to change the >cost of doing an index lookup when the index is linearly ordered. I'm not sure I understand what you mean by "index is linearly ordered", but I guess correlation is that statistic you are talking about. However, it is calculated per column, not per index. >Whether CLUSTER does this or not, I don't know, If you CLUSTER on an index and then ANALYSE, you get a correlation of 1.0 (== optimum) for the first column of the index. > I never heard back >from him after getting the runtime down to a few ms. :-/ Pity! I'd have liked to see EXPLAIN ANALYSE for SELECT * FROM mss_fwevent WHERE sensorid = 7 AND evtime > (now() - '6 hours'::INTERVAL) AND NOT action; SELECT * FROM mss_fwevent WHERE sensorid = 7 AND evtime > (now() - '6 hours'::INTERVAL); SELECT * FROM mss_fwevent WHERE evtime > (now() - '6 hours'::INTERVAL); SELECT * FROM mss_fwevent WHERE sensorid = 7; > Are indexes >on linearly ordered data rebalanced somehow? I thought CLUSTER only >reordered data on disk. -sc AFAIK CLUSTER re-creates all indices belonging to the table. Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Serious issues with CPU usage
On Mon, 8 Sep 2003 13:50:23 +0300, Andri Saar <[EMAIL PROTECTED]> wrote: >Basically I do this: >1) select about ~700 ID's I have to poll >2) poll them >3) update those 700 rows in that "table" I used (~2700 rows total). > >And I do this cycle once per minute, so yes, I've got a zillion updates. 700 >of 2700 is roughly 25%, so I'd have to vacuum once per minute? With such a small table VACUUM should be a matter of less than one second: fred=# vacuum verbose t; INFO: --Relation public.t-- INFO: Index t_pkey: Pages 65; Tuples 16384: Deleted 4096. CPU 0.01s/0.10u sec elapsed 0.21 sec. INFO: Removed 4096 tuples in 154 pages. CPU 0.04s/0.02u sec elapsed 0.07 sec. INFO: Pages 192: Changed 192, Empty 0; Tup 16384: Vac 4096, Keep 0, UnUsed 0. Total CPU 0.08s/0.16u sec elapsed 0.36 sec. VACUUM Time: 415.00 ms And this is on a 400 MHz machine under cygwin, so don't worry if you have a real computer. Servus Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Inconsistent performance
On Mon, 15 Sep 2003 22:26:45 -0400, Christopher Browne <[EMAIL PROTECTED]> wrote: >> select count (*) from table; >The only possible plan for THAT query will involve a seq scan of the >whole table. If the postmaster already has the data in cache, it >makes sense for it to run in 1 second. If it has to read it from >disk, 12 seconds makes a lot of sense. Yes. And note that the main difference is between having the data in memory and having to fetch it from disk. I don't believe that this difference can be explained by 9000 read calls hitting the operating system's cache. >You might want to increase the "shared_buffers" parameter in >postgresql.conf; that should lead to increased stability of times as >it should be more likely that the data in "table" will remain in >cache. Let's not jump to this conclusion before we know what's going on. Joseph Bove <[EMAIL PROTECTED]> wrote in another message above: | I did have shared_buffers and sort_mem both set higher originally (15000, | 32168) As I read this I think he meant "... and had the same performance problem." Joseph, what do you get, if you run that EXPLAIN ANALYSE SELECT count(*) ... several times? What do vmstat and top show while the query is running? Are there other processes active during or between the runs? What kind of processes? Postgres backends? Web server? ... Servus Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] rewrite in to exists?
On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne" <[EMAIL PROTECTED]> wrote: >Why can't you just go: > >select code, id, name, date_of_service from tbl where xxx <= 29800 and xx >= >29909 and code='XX' and client_code='XX' order by id, date_of_service; Because (ignoring conditions on code and client_code for a moment) if for a given date there is at least one row satisfying the condition on xxx, the original query returns *all* rows having this date, regardless of their xxx value. For example: id | date | xxx ++--- 1 | 2003-01-01 | 1* 2 | 2003-01-01 | 29800 ** 3 | 2003-01-01 | 3* 4 | 2003-02-02 | 2 5 | 2003-03-03 | 29900 ** >> select code, id, name, date_of_service >> from tbl >> where date_of_service in >> (select date_of_service >> from tbl >> where xxx >= '29800' >> and xxx <= '29909' >> and code = 'XX') >> and client_code = 'XX' >> order by id, date_of_service; To the original poster: You did not provide a lot of information, but the following suggestions might give you an idea ... SELECT code, id, date_of_service FROM tbl WHERE EXISTS (SELECT * FROM tbl t2 WHERE t2.xxx >= '29800' AND t2.xxx <= '29909' AND t2.code = 'XX' AND tbl.date_of_service = t2.date_of_service) AND client_code = 'XX' ORDER BY id, date_of_service; SELECT t1.code, t1.id, t1.date_of_service FROM tbl t1 INNER JOIN (SELECT DISTINCT date_of_service FROM tbl WHERE xxx >= '29800' AND xxx <= '29909' AND code = 'XX' ) AS t2 ON (t1.date_of_service = t2.date_of_service) WHERE t1.client_code = 'XX' ORDER BY id, date_of_service; SELECT DISTINCT t1.code, t1.id, t1.date_of_service FROM tbl AS t1 INNER JOIN tbl AS t2 ON (t1.date_of_service = t2.date_of_service AND t2.xxx >= '29800' AND t2.xxx <= '29909' AND t2.code = 'XX') WHERE t1.client_code = 'XX' -- might as well put this -- condition into the ON clause ORDER BY id, date_of_service; The last one assumes that there are no duplicates on code, id, date_of_service in the desired result. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] rewrite in to exists?
On Thu, 18 Sep 2003 12:27:23 -0700 (GMT-07:00), LN Cisneros <[EMAIL PROTECTED]> wrote: >But, the EXISTS version doesn't Laurette, looking at that SELECT statement again I can't see what's wrong with it. One of us is missing something ;-) > really give me what I want... Can you elaborate? SELECT code, id, date_of_service FROM tbl WHERE EXISTS (SELECT * FROM tbl t2 WHERE t2.xxx >= '29800' AND t2.xxx <= '29909' AND t2.code = 'XX' AND tbl.date_of_service = t2.date_of_service) -- (!) AND client_code = 'XX' ORDER BY id, date_of_service; >all rows in tbl that ^^^ Well, all that have client_code = 'XX', as in your original query. > match the date of the subquery. The matching is done by the line with the (!) comment. Servus Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] osdl-dbt3 run results - puzzled by the execution plans
On Thu, 18 Sep 2003 15:36:50 -0700, Jenny Zhang <[EMAIL PROTECTED]>
wrote:
>We thought the large effective_cache_size should lead us to better
>plans. But we found the opposite.
The common structure of your query plans is:
Sort
Sort Key: sum((partsupp.ps_supplycost * partsupp.ps_availqty))
InitPlan
-> Aggregate
-> SubPlan
-> Aggregate
Filter: (sum((ps_supplycost * ps_availqty)) > $0)
-> Group
-> Sort
Sort Key: partsupp.ps_partkey
-> SubPlan (same as above)
where the SubPlan is
-> Merge Join (cost=519.60..99880.05 rows=32068 width=65)
(actual time=114.78..17435.28 rows=30400 loops=1)
ctr=5.73
Merge Cond: ("outer".ps_suppkey = "inner".s_suppkey)
-> Index Scan using i_ps_suppkey on partsupp
(cost=0.00..96953.31 rows=801712 width=34)
(actual time=0.42..14008.92 rows=799361 loops=1)
ctr=6.92
-> Sort (cost=519.60..520.60 rows=400 width=31)
(actual time=106.88..143.49 rows=30321 loops=1)
ctr=3.63
Sort Key: supplier.s_suppkey
-> SubSubPlan
for large effective_cache_size and
-> Nested Loop (cost=0.00..130168.30 rows=32068 width=65)
(actual time=0.56..1374.41 rows=30400 loops=1)
ctr=94.71
-> SubSubPlan
-> Index Scan using i_ps_suppkey on partsupp
(cost=0.00..323.16 rows=80 width=34)
(actual time=0.16..2.98 rows=80 loops=380)
ctr=108.44
Index Cond: (partsupp.ps_suppkey = "outer".s_suppkey)
for small effective_cache_size. Both subplans have an almost
identical subsubplan:
-> Nested Loop (cost=0.00..502.31 rows=400 width=31)
(actual time=0.23..110.51 rows=380 loops=1)
ctr=4.55
Join Filter: ("inner".s_nationkey = "outer".n_nationkey)
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=10)
(actual time=0.08..0.14 rows=1 loops=1)
ctr=9.36
Filter: (n_name = 'ETHIOPIA'::bpchar)
-> Seq Scan on supplier (cost=0.00..376.00 rows=1 width=21)
(actual time=0.10..70.72 rows=1 loops=1)
ctr=5.32
I have added the ctr (cost:time ratio) for each plan node. These
values are mostly between 5 and 10 with two notable exceptions:
1) -> Sort (cost=519.60..520.60 rows=400 width=31)
(actual time=106.88..143.49 rows=30321 loops=1)
ctr=3.63
It has already been noticed by Matt Clark that this is the only plan
node where the row count estimation looks wrong. However, I don't
believe that this has great influence on the total cost of the plan,
because the ctr is not far from the usual range and if it were a bit
higher, it would only add a few hundred cost units to a branch costing
almost 10 units. BTW I vaguely remember that there is something
strange with the way actual rows are counted inside a merge join.
Look at the branch below this plan node: It shows an actual row count
of 380.
2) -> Index Scan using i_ps_suppkey on partsupp
(cost=0.00..323.16 rows=80 width=34)
(actual time=0.16..2.98 rows=80 loops=380)
ctr=108.44
Here we have the only plan node where loops > 1, and it is the only
one where the ctr is far off. The planner computes the cost for one
loop and multiplies it by the number of loops (which it estimates
quite accurately to be 400), thus getting a total cost of ca. 13.
We have no reason to believe that the single loop cost is very far
from reality (for a *single* index scan), but the planner does not
account for additional index scans hitting pages in the cache that
have been brought in by preceding scans. This is a known problem, Tom
has mentioned it several times, IIRC.
Now I'm very interested in getting a better understanding of this
problem, so could you please report the results of
. \d i_ps_suppkey
. VACUUM VERBOSE ANALYSE partsupp;
VACUUM VERBOSE ANALYSE supplier;
. SELECT attname, null_frac, avg_witdh, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'partsupp' AND attname IN ('ps_suppkey', ...);
Please insert other interesting column names for ..., especially
those contained in i_ps_suppkey, if any.
. SELECT relname, relpages, reltuples
FROM pg_class
WHERE relname IN ('partsupp', 'supplier', ...);
^^^
Add relevant index names here.
. EXPLAIN ANALYSE
SELECT ps_partkey, ps_supplycost, ps_availqty
FROM partsupp, supplier
WHERE ps_suppkey = s_suppkey AND s_nationkey = '';
The idea is to eliminate parts of the plan that are always the same.
Omitting nation is possibly to much a simplification. In this case
pleas
Re: [PERFORM] osdl-dbt3 run results - puzzled by the execution
On Fri, 19 Sep 2003 11:35:35 -0700, Jenny Zhang <[EMAIL PROTECTED]> wrote: >I posted more results as you requested: Unfortunately they only confirm what I suspected earlier: >> 2) -> Index Scan using i_ps_suppkey on partsupp >> (cost=0.00..323.16 rows=80 width=34) >> (actual time=0.16..2.98 rows=80 loops=380) >> ctr=108.44 >> the planner does not >> account for additional index scans hitting pages in the cache that >> have been brought in by preceding scans. This is a known problem PF1 = estimated number of page fetches for one loop ~ 320 L = estimated number of loops ~ 400 P = number of pages in relation ~ 21000 Cutting down the number of heap page fetches if PF1 * L > P and P < effective_cache_size seems like an obvious improvement, but I was not able to figure out where to make this change. Maybe it belongs into costsize.c near run_cost += outer_path_rows * (inner_path->total_cost - inner_path->startup_cost) * joininfactor; in cost_nestloop() or it should be pushed into the index cost estimation functions. Hackers? For now you have to keep lying about effective_cache_size to make the planner overestimate merge joins to compensate for the planner's overestimation of nested loops. Sorry for having no better answer. Servus Manfred ---(end of broadcast)--- TIP 3: 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] Optimizing >= and <= for numbers and dates
On Wed, 1 Oct 2003 19:45:29 +0200 (MEST), "Dimitri Nagiev" <[EMAIL PROTECTED]> wrote: >template1=# explain analyze select * from mytable where >mydate>='2003-09-01'; > Seq Scan on mytable (cost=0.00..2209.11 rows=22274 width=562) (actual > time=0.06..267.30 rows=22677 loops=1) > Filter: (mydate >= '2003-09-01'::date) > Total runtime: 307.71 msec Didn't you say that there are 25000 rows in the table? I can't believe that for selecting 90% of all rows an index scan would be faster. Try SET enable_seqscan = 0; explain analyze select * from mytable where mydate>='2003-09-01'; If you find the index scan to be faster, there might be lots of dead tuples in which case you should VACUUM FULL mytable; Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] planner doesn't use multicolumn index
On Wed, 8 Oct 2003 09:08:59 -0500 (CDT), Adrian Demaestri <[EMAIL PROTECTED]> wrote: >the type of the fields are int2 and >int4, the where condition is v.g. partido=99 and partida=123). Write your search condition as WHERE partido=99::int2 and partida=123 Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
