Re: [HACKERS] Ingres to be released as open source
Like the article says, I wonder if these is any synergy between the products. ie. Can we grab features from their codebase? Wouldn't there be a license problem ? Yes, Ingres may be able to pull features from PostgreSQL, but maybe it doesn't work the other way. -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 12.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLørdag 12.00-16.00 Web: www.suse.dk ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Timezone fun (bugs and a request)
LOG: database system was shut down at 2004-05-25 15:15:44 GMT-12 For comparison, 7.4.1 on the same system says: LOG: database system was shut down at 2004-05-25 16:03:43 NZST Can we keep the zic database convention unchanged but change the display format in the logs to be consistent with the SQL conventions? I think what was meant is, that if it displays GMT+-something it should convert the sign. Is that zic filename exposed anywhere else ? It is common practice that + is East and - is West, no ? Andreas ---(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: [HACKERS] Timezone fun (bugs and a request)
Zeugswetter Andreas SB SD wrote: LOG: database system was shut down at 2004-05-25 15:15:44 GMT-12 For comparison, 7.4.1 on the same system says: LOG: database system was shut down at 2004-05-25 16:03:43 NZST Can we keep the zic database convention unchanged but change the display format in the logs to be consistent with the SQL conventions? I think what was meant is, that if it displays GMT+-something it should convert the sign. Is that zic filename exposed anywhere else ? It is common practice that + is East and - is West, no ? Yes, exactly. Regardless of how the timezone is handled internally, showing 12-hours-east as GMT-12 in logs is horribly confusing. -O ---(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: [HACKERS] Optimizer bug??
Hi All, EXPLAIN ANALYZE SELECT COUNT(*) AS c FROM articletbl WHERE (tarih BETWEEN '2004-04-24' AND '2004-05-24') result is : QUERY PLAN Aggregate (cost=40987.02..40987.02 rows=1 width=0) (actual time=39209.476..39209.478 rows=1 loops=1) - Seq Scan on articletbl (cost=0.00..40947.39 rows=15851 width=0) (actual time=1233.369..39153.741 rows=19515 loops=1) Filter: ((tarih = '2004-04-24'::date) AND (tarih = '2004-05-24'::date)) Total runtime: 39210.077 ms I use fedora core 1 SMP kernel Configuration : Dual PIII-550 Dual SCSI (10ms. access time i think) 3x128 mb. SDRAM Regards Ismail Kizir - Original Message - From: Stephan Szabo [EMAIL PROTECTED] To: Ismail Kizir [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, May 24, 2004 11:52 PM Subject: Re: [HACKERS] Optimizer bug?? On Mon, 24 May 2004, Ismail Kizir wrote: Hi all, give us the result of these queries: SELECT COUNT(*) FROM articletbl; 268726 records, it takes 34169 ms. to compute this SELECT COUNT(*) AS c FROM articletbl WHERE mydate BETWEEN '2004-04-24' AND '2004-05-24'; 18982 records, it takes 34249 ms. to compute this. SELECT COUNT(*) AS c FROM articletbl WHERE mydate = '2004-04-24'; 850 records, it takes only 40 ms. to compute this. It is evident that there is a problem here! Am I wrong?? What does explain analyze show for the between query (not just explain) and what does it show if enable_seqscan is set to false? It's possible that it's badly overestimating the cost of the range query, but that's hard to say at this point. There is a point at which in general an index scan becomes more costly than a sequence scan, and it's possible to move that point by changing optimizer settings in the configuration. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Optimizer bug??
Gaetano, I've changed my settings as : #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: #effective_cache_size = 1000# typically 8KB each random_page_cost = 2# units are one sequential page fetch cost cpu_tuple_cost = 0.009 # (same) cpu_index_tuple_cost = 0.0009 # (same) cpu_operator_cost = 0.0025 # (same) # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each But it still doesn't optimize for that range. Finally, i've set seq_scan off and, it works now. But i think, there must be a way to handle those settings automatically for cpu, ram and hdd settings(is it a sweet dream??) Thanks Ismail Kizir - Original Message - From: Gaetano Mendola [EMAIL PROTECTED] Newsgroups: comp.databases.postgresql.hackers To: Ismail Kizir [EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 12:47 AM Subject: Re: Optimizer bug?? Ismail Kizir wrote: Hi all, give us the result of these queries: SELECT COUNT(*) FROM articletbl; 268726 records, it takes 34169 ms. to compute this SELECT COUNT(*) AS c FROM articletbl WHERE mydate BETWEEN '2004-04-24' AND '2004-05-24'; 18982 records, it takes 34249 ms. to compute this. SELECT COUNT(*) AS c FROM articletbl WHERE mydate = '2004-04-24'; 850 records, it takes only 40 ms. to compute this. It is evident that there is a problem here! Am I wrong?? Try to do an explain analyze for both queries, and repeat the explain analyze but before disable the sequential scan ( set enable_seq_scan=off ). For sure what do you have is extimated cost higher, what do you have to do is decrease that cost, try with these values: random_page_cost = 2.5 cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.0025 these are values that are good for a modern machine. Hackers, what about to decrease the default values for this quantities ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_ctl.c
Bruce Momjian wrote: I am almost finished Andrew's version of pg_ctl.c. Here is the current version I am using. Sorry, I should have sent this to patches instead. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Timezone fun (bugs and a request)
Oliver Jowett wrote: Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Also, unless I'm missing something, shouldn't Chile (Alvaro's timezone?) be behind GMT (GMT-something) not ahead of it (GMT+something)? Part of the confusion here is that the zone names in the zic database follow POSIX rules: plus is west of Greenwich. AFAICS it's doing the right thing in selecting Etc/GMT-12 for you. Ew! That's disgusting! What possessed POSIX to do this the opposite way to pretty much everything else? You want ugly, look at how we find the system timezone --- we scan forward for 14 months looking at the timezone abbreviations returned by the operating system. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timezone fun (bugs and a request)
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: It is common practice that + is East and - is West, no ? The SQL standard says that. The POSIX standard says the opposite. Most of the Unixen I'm familiar with follow POSIX when choosing time zone names. The zic database is in itself a de facto standard (it's used verbatim in glibc, I believe, and on several non-glibc platforms). So I'm rather hesitant to make any unilateral changes in it. I would like to think that we will be able to just drop in any updates that occur from time to time in the zone database files. Politicians being what they are, you can be certain there will be changes to track. Also, it's worth pointing out here that falling back to Etc/GMT+/-n is intended to be just that, a last-ditch fallback that won't be seen in normal practice. We still need to do some more work on identify_system_timezone() to make that happen outside North America, but I would like to think that average users will never see these zones except by deliberate selection. So I think it's not worth getting worked up about what the sign convention is. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer bug??
Ismail Kizir wrote: Gaetano, I've changed my settings as : #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: #effective_cache_size = 1000# typically 8KB each random_page_cost = 2# units are one sequential page fetch cost cpu_tuple_cost = 0.009 # (same) cpu_index_tuple_cost = 0.0009 # (same) cpu_operator_cost = 0.0025 # (same) # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each But it still doesn't optimize for that range. Finally, i've set seq_scan off and, it works now. But i think, there must be a way to handle those settings automatically for cpu, ram and hdd settings(is it a sweet dream??) Did you SIGHUP the postmaster after ? You can change these settings also from command line, what you have to do is decrease the cost of the index scan till is less of the sequential scan cost. Good luck. Regards Gaetano Mendola ---(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: [HACKERS] Optimizer bug??
Gaetano, Hackers, what about to decrease the default values for this quantities ? Oh, I don't think Ismail has a bug, I think he probably just needs to tune his database. Ismail, join the PGSQL-PERFORMANCE mailing list and discuss this over there. -HACKERS is not the appropriate place for a tuning discussion. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Optimizer bug??
Josh Berkus wrote: Gaetano, Hackers, what about to decrease the default values for this quantities ? Oh, I don't think Ismail has a bug, I think he probably just needs to tune his database. I just only suggesting to decrease that values that are oversized for a modern hardware. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Optimizer bug??
Thanks Josh, I'll subscribe to that group. Regards Ismail Kizir - Original Message - From: Josh Berkus [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Ismail Kizir [EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 8:34 PM Subject: Re: [HACKERS] Optimizer bug?? Gaetano, Hackers, what about to decrease the default values for this quantities ? Oh, I don't think Ismail has a bug, I think he probably just needs to tune his database. Ismail, join the PGSQL-PERFORMANCE mailing list and discuss this over there. -HACKERS is not the appropriate place for a tuning discussion. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Optimizer bug??
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Berkus wrote: | Gaetano, | | |I just only suggesting to decrease that values that are oversized for a | | modern | |hardware. | | | Hey, have you had success with those settings that you suggested? I've tried | tinkering with the relative CPU cost settings, and had mixed results. | That's why I have no particular recommmendation for them. | Usually yes, decreasing that values I'm able to decrease the index scan cost, so when I enable again the sequential scan the index one is choosed. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAs5Je7UpzwH2SGd4RAg/1AKCUYdTGIm5c7kG/ZXvmb49RWybs2ACgg3Wk zOHkWnCvbyPgeDCU3pn6UfQ= =Td0B -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Timezone fun (bugs and a request)
I wrote: Also, it's worth pointing out here that falling back to Etc/GMT+/-n is intended to be just that, a last-ditch fallback that won't be seen in normal practice. We still need to do some more work on identify_system_timezone() to make that happen outside North America, but I would like to think that average users will never see these zones except by deliberate selection. BTW, as of an hour or so ago, identify_system_timezone is a bit smarter than before. Please try it and see if it gets it right on your machine. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Optimizer bug??
Gaetano Mendola [EMAIL PROTECTED] writes: I just only suggesting to decrease that values that are oversized for a modern hardware. I've seen no evidence saying that random_page_cost needs to be decreased for modern hardware. Disk seek speed versus bandwidth hasn't changed that much. People sometimes find it profitable to decrease that setting to compensate for other optimizer issues, but that doesn't mean we ought to change the default. As for the other settings you mentioned, I'd agree that the defaults are pretty arbitrary, but what evidence have you got to suggest better ones? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Optimizer Bug issue
Tom, You say that default values for optimizer are efficient. But this is evident that it doesn'tfunction as expectedin my case. I have a database of 20 tables, ~1gb total size. My biggest table contains ~270,000 newspaper article from Turkish journals. I am actually working on "fulltext search" programof my own. There is a problem here. Ifthat values arenot designed for an "ordinary" database application. I am asking to myself for which case they are designed for. I didn't write to "hackers" list only for myself, but for everyone of us too. Please examine the values in my messages in past 2 days, you'll see what i mean. I am sure default values are erronous. Regards Ismail Kizir
Re: [HACKERS] Timezone fun (bugs and a request)
Oliver Jowett [EMAIL PROTECTED] writes: Yes, exactly. Regardless of how the timezone is handled internally, showing 12-hours-east as GMT-12 in logs is horribly confusing. Well, uh, you could always just pretend it was really 12-hours-west... That doesn't help people in other time zones though :) -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Timezone fun (bugs and a request)
On Tue, May 25, 2004 at 03:01:24PM -0400, Tom Lane wrote: I wrote: Also, it's worth pointing out here that falling back to Etc/GMT+/-n is intended to be just that, a last-ditch fallback that won't be seen in normal practice. We still need to do some more work on identify_system_timezone() to make that happen outside North America, but I would like to think that average users will never see these zones except by deliberate selection. BTW, as of an hour or so ago, identify_system_timezone is a bit smarter than before. Please try it and see if it gets it right on your machine. Works here perfectly, thank you. I changed the system date to see if it worked on DST too, and it does. I can also attest that it takes a very short time to find the correct timezone, and this is a very slow machine (Intel Celeron 533). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No renuncies a nada. No te aferres a nada. ---(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: [HACKERS] Optimizer Bug issue
Ismail Kizir [EMAIL PROTECTED] writes: I have a database of 20 tables, ~1gb total size. My biggest table contains ~270,000 newspaper article from Turkish journals. I am actually working on fulltext search program of my own. How much RAM does the machine have? Have you already executed the query and are repeating it? It's likely the entire data set is cached in RAM. That's not the long-term average as your data set grows. The numbers there are appropriate for a database where the data being fetched cannot all fit in RAM and isn't all pre-cached. There are also scenarios where the algorithms the optimizer uses to estimate costs don't capture everything. tweaking the parameters to correct for these problems would cause other queries to be handled even worse. If anything the penalty for random disk accesses has increased over the years. My desktop is about 100 times faster than my 486 router. But the hard drive in the 486 is only about 10x slower than the hard drive in the desktop. And the ratio of seek times is probably even less. There is a parameter effective_cache_size which is supposed to help Postgres take into account the likelihood that the data will already be in cache. How exactly does this affect planning and perhaps this parameter needs to have much more impact on the resultant plans. At least for databases that are small relative to it. -- greg ---(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: [HACKERS] New horology failure
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Well, in the case you have an install prefix of /usr, we wouldn't want relative installs because you would have /usr/bin and /usr/lib/postgresql and that wouldn't be relocatable. Why not? ISTM that the algorithm should go something like this: 1. Take PGBINDIR and strip off the last component (presumably bin). 2. See if this matches the configured SHAREDIR, PKGLIBDIR, or whatever *for as many components as are in the stripped BINDIR*. Do not assume that SHAREDIR must have the same number of components. 3. If match, then what you should do is use the remaining unmatched part of SHAREDIR etc as what to append to the trimmed exec_path. For example given the test situation I had: PGBINDIR = '/home/tgl/testversion/bin' PGSHAREDIR = '/home/tgl/testversion/share/postgresql' my_exec_path = '/home/tgl/pgsql-list/src/test/regress/./tmp_check/install//home/tgl/testversion/bin/postmaster' ret_path = '/home/tgl/testversion/share/postgresql' what *should* have happened was compare /home/tgl/testversion to /home/tgl/testversion, decide they agree, and extract share/postgresql as the part to attach to my_exec_path. Then you'd strip two components from my_exec_path (bin and program name) and attach share/postgresql. Voila ... right answer. OK, I have committed this change. Please let me know if it doesn't work the way you wanted. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
[HACKERS] another optimizer bug?
Following example is with latest anonymous cvs of 7.5. I can't get LIKE to use an index even with seq_scan = off. I'm using the default locale and hchassis.vin_no is defined as char(17). The hchassis table has about 250k rows in it. The non aggregate versions of the selects have the same results WRT the optimizer. Varying the VIN makes no difference. Simple form: select a from b where a like 'k%'; Am I crazy? This is a query I would normally expect to always use the index. Merlin Log: [first two queries with like, second two with =] cpc=# explain select count(vin_no) from hchassis where vin_no like '2FTZX08W8WCA21580%'; QUERY PLAN --- Aggregate (cost=19576.22..19576.22 rows=1 width=21) - Seq Scan on hchassis (cost=0.00..19576.21 rows=1 width=21) Filter: (vin_no ~~ '2FTZX08W8WCA21580%'::text) (3 rows) cpc=# select count(vin_no) from hchassis where vin_no like '2FTZX08W8WCA21580%'; count --- 1 (1 row) cpc=# cpc=# explain select count(vin_no) from hchassis where vin_no = '2FTZX08W8WCA21580'; QUERY PLAN --- Aggregate (cost=5.61..5.61 rows=1 width=21) - Index Scan using hchassis_vin_no_idx on hchassis (cost=0.00..5.60 rows=1 width=21) Index Cond: (vin_no = '2FTZX08W8WCA21580'::bpchar) (3 rows) count --- 1 (1 row) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [pgsql-hackers-win32] another optimizer bug?
I've spoken to Merlin off-list and confirmed this issue will be fixed in the locale fix I'll post shortly. The reason being that the backend thought it was in a non-C locale (the window system default locale which is specifically *not* C), and in non-C locale LIKE is not indexable. //Magnus -Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] Sent: den 25 maj 2004 22:53 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: [pgsql-hackers-win32] another optimizer bug? Following example is with latest anonymous cvs of 7.5. I can't get LIKE to use an index even with seq_scan = off. I'm using the default locale and hchassis.vin_no is defined as char(17). The hchassis table has about 250k rows in it. The non aggregate versions of the selects have the same results WRT the optimizer. Varying the VIN makes no difference. Simple form: select a from b where a like 'k%'; Am I crazy? This is a query I would normally expect to always use the index. Merlin Log: [first two queries with like, second two with =] cpc=# explain select count(vin_no) from hchassis where vin_no like '2FTZX08W8WCA21580%'; QUERY PLAN --- Aggregate (cost=19576.22..19576.22 rows=1 width=21) - Seq Scan on hchassis (cost=0.00..19576.21 rows=1 width=21) Filter: (vin_no ~~ '2FTZX08W8WCA21580%'::text) (3 rows) cpc=# select count(vin_no) from hchassis where vin_no like '2FTZX08W8WCA21580%'; count --- 1 (1 row) cpc=# cpc=# explain select count(vin_no) from hchassis where vin_no = '2FTZX08W8WCA21580'; QUERY PLAN --- - --- Aggregate (cost=5.61..5.61 rows=1 width=21) - Index Scan using hchassis_vin_no_idx on hchassis (cost=0.00..5.60 rows=1 width=21) Index Cond: (vin_no = '2FTZX08W8WCA21580'::bpchar) (3 rows) count --- 1 (1 row) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Optimizer bug??
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I just only suggesting to decrease that values that are oversized for a modern hardware. I've seen no evidence saying that random_page_cost needs to be decreased for modern hardware. Disk seek speed versus bandwidth hasn't changed that much. People sometimes find it profitable to decrease that setting to compensate for other optimizer issues, but that doesn't mean we ought to change the default. As for the other settings you mentioned, I'd agree that the defaults are pretty arbitrary, but what evidence have you got to suggest better ones? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org I had queries not using a index scan ( was the best method ) that started to use the index scan decreasing that values. What I also notice is that under certain values I'm not able to decrease anymore the cost of a query. I'm using now: random_page_cost = 2.0 cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.0025 vs these default costs: #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) look at this two queries (I just pick up one): DEFAULT VALUES test=# explain analyze select * from v_psr_guide_web; QUERY PLAN -- Subquery Scan v_psr_guide_web (cost=750.58..750.85 rows=21 width=236) (actual time=196.420..197.210 rows=178 loops=1) - Sort (cost=750.58..750.64 rows=21 width=236) (actual time=196.411..196.532 rows=178 loops=1) Sort Key: vg.estimated_start - Hash Join (cost=717.57..750.12 rows=21 width=236) (actual time=190.489..195.817 rows=178 loops=1) Hash Cond: (outer.id_publisher = inner.id_publisher) - Hash Left Join (cost=716.17..747.87 rows=128 width=208) (actual time=190.288..194.757 rows=178 loops=1) Hash Cond: (outer.id_drm_service = inner.id_drm_service) - Hash Left Join (cost=715.12..746.15 rows=128 width=188) (actual time=189.978..193.734 rows=178 loops=1) Hash Cond: (outer.id_cas_service = inner.id_cas_service) - Subquery Scan vg (cost=85.34..87.57 rows=127 width=168) (actual time=125.807..128.751 rows=178 loops=1) Filter: (view_target_group 2) - Unique (cost=85.34..85.98 rows=127 width=324) (actual time=125.743..127.723 rows=192 loops=1) - Sort (cost=85.34..85.66 rows=127 width=324) (actual time=125.738..126.573 rows=1298 loops=1) Sort Key: vp.id_package, s.estimated_start, sl.end_date - Hash Join (cost=71.92..80.91 rows=127 width=324) (actual time=103.605..118.505 rows=1298 loops=1) Hash Cond: (outer.id_package = inner.id_package) - Subquery Scan vp (cost=37.86..42.66 rows=384 width=304) (actual time=97.514..100.926 rows=384 loops=1) - Sort (cost=37.86..38.82 rows=384 width=219) (actual time=97.488..97.744 rows=384 loops=1) Sort Key: p.id_publisher, p.name - Hash Left Join (cost=1.96..21.37 rows=384 width=219) (actual time=1.003..95.690 rows=384 loops=1) Hash Cond: (outer.id_package = inner.id_package) - Seq Scan on packages p (cost=0.00..13.84 rows=384 width=203) (actual time=0.005..0.780 rows=384 loops=1) - Hash (cost=1.77..1.77 rows=77 width=20) (actual time=0.214..0.214 rows=0 loops=1) - Seq Scan on package_security ps (cost=0.00..1.77 rows=77 width=20) (actual time=0.011..0.126 rows=77 loops=1) - Hash (cost=33.81..33.81 rows=102 width=24) (actual time=5.756..5.756 rows=0 loops=1) - Hash Join (cost=17.45..33.81 rows=102 width=24) (actual time=1.625..4.216 rows=1298
Re: [HACKERS] Current CVS tip segfaulting
On Fri, Apr 23, 2004 at 05:10:34PM -0400, Alvaro Herrera wrote: In current (as of a couple hours ago) clean CVS tip sources, without any of my local changes, I'm getting a postmaster segfault when trying to connect to a non existant database. Just to follow up, I no longer see this problem in CVS tip. I don't know if somebody fixed it on purpose, but my system is the same as before and I can't reproduce the bug anymore. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El hombre nunca sabe de lo que es capaz hasta que lo intenta (C. Dickens) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer Bug issue
Ismail Kizir [EMAIL PROTECTED] writes: I am sure default values are erronous. Perhaps they are --- for your database, on your hardware. Or perhaps the problem is somewhere else (we know that the costing of nestloop indexscan joins needs work, for instance). But in any case there's a reason why these numbers are tweakable. It doesn't follow that the defaults should be changed. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone fun (bugs and a request)
Tom Lane wrote: BTW, as of an hour or so ago, identify_system_timezone is a bit smarter than before. Please try it and see if it gets it right on your machine. Looks good: LOG: database system was shut down at 2004-05-26 12:43:55 NZST test=# select now(); now -- 2004-05-26 12:44:30.00082+12 (1 row) -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] log_statement and Parse/Bind
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: I notice that when using the extended query protocol, statement logging appears to happen only when a Parse message is received. This is less than ideal: I agree, but I didn't have time at the end of the 7.4 development cycle to work out what should happen. I do not think that regurgitating the statement three times would make anyone very happy, so some thought has to go into what's appropriate. Currently the statement-type-analysis and logging occur in pg_parse_query, which is called from: pg_parse_and_rewrite from fmgr_sql_validator (parses for syntax/args/return types only) from init_sql_fcache (parses for syntax/args/return types only) exec_simple_query (1) exec_parse_message (2) fmgr_sql_validator (parses for syntax only) _SPI_execute (3) Turning off logging in pg_parse_query only affects (1..3), the other cases we don't really want to log anyway (except perhaps in the case of an error). For (1) we can log after the parse but before execution starts. For (2) we can delay logging until the Execute message comes along; the original query text is stored in the portal. For (3) we can delay logging until _SPI_execute_plan (or do it directly in _SPI_execute when plan == NULL); the original query text is stored in the _SPI_Plan. Add a parse tree - statement type helper function to do categorization of the parse trees (currently done in pg_parse_query). Store that type in the prepared statement (for exec_parse_message) or _SPI_Plan (for _SPI_execute) for later use when executing the query; exec_simple_query can use the result immediately. This leaves only error handling. Currently, with log_statement = all statements get logged before parsing which is quite useful when tracking down parse errors. This doesn't work if we only log when the query is actually executed, since a query that doesn't parse can never get executed. Maybe something like including the original query string as a field of errors encountered when parsing would do the trick? Or use an error callback to catch the error and log the statement before the real error is reported when log_statement = all? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Optimizer Bug issue
I am actually working on fulltext search program of my own. No need, use contrib/tsearch2 Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [COMMITTERS] pgsql-server: Add code to identify_system_timezone() to try all zones in
On Tue, May 25, 2004 at 03:08:59PM -0300, Tom Lane wrote: Add code to identify_system_timezone() to try all zones in the zic database, not just ones that we cons up POSIX names for. This looks grim but it seems to take less than a second even on a relatively slow machine, and since it only happens once during postmaster startup, that seems acceptable. Is it possible to execute this at initdb time to set the default timezone in postgresql.conf? The user could get a message about what timezone was selected, and be offered a --command-line-switch to select a specific one. Just an idea ... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Ninguna manada de bestias tiene una voz tan horrible como la humana (Orual) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [COMMITTERS] pgsql-server: Add code to identify_system_timezone()
Alvaro Herrera wrote: On Tue, May 25, 2004 at 03:08:59PM -0300, Tom Lane wrote: Add code to identify_system_timezone() to try all zones in the zic database, not just ones that we cons up POSIX names for. This looks grim but it seems to take less than a second even on a relatively slow machine, and since it only happens once during postmaster startup, that seems acceptable. Is it possible to execute this at initdb time to set the default timezone in postgresql.conf? The user could get a message about what timezone was selected, and be offered a --command-line-switch to select a specific one. Just an idea ... But if they change the OS timezone, we need to track that on postmaster restart. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] list rewrite committed
I've applied the list rewrite patch to CVS HEAD. I've also sent a copy of the patch I applied to the -patches list. Notes: - the tree compiles without warnings and passes the regression tests. I'm not aware of any bugs, regression failures, or compiler warnings caused by the list rewrite patch -- please let me know if you encounter anything - client code that uses the List API is _not_ source compatible with the new List API. The most common change will be the need to change the foreach() iteration variable to a ListCell*, rather than a List*. There are also some subtle changes in behavior: for example, lcons() is now destructive (in the sense that you cannot call it on a list and expect the list you pass to lcons() to be unmodified; this was the case with the old list API) Remaining work: - investigate inline functions for non-GCC compilers - disable the use of the compatibility API throughout the tree and change the code over to use the new API function names. This ought to be largely a mechanical search and replace operation -- any patches are welcome. I plan to start converting the remainder of the tree in this fashion tomorrow. - use the new for_each_cell() and forboth() macros throughout the tree, as appropriate. - remove the FastList API -- there is no need for it anymore. Tom, would you like to do this or should I? - anything else? Thanks to Tom and Alvaro for their assistance in completing this work. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] list rewrite committed
- anything else? All compiles and passes regression tests on FreeBSD... Chris ---(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
