[GENERAL] Problem with newline/tab completing with psql
Hi, I have a problem with newline or tab completing when adding a generated FUNCTION via the psql interface (have tried pasting and adding it with -f rule.sql) parser stack overflow at or near THEN LINE 5002: ELSIF ( NEW.a between 1246566738 and 1247010220 ) THEN Have also tried using the -n option with psql and set disable-completion on in .inputrc Is there any other method to solve this problem? -- Håvard Wahl Kongsgård -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convert mysql to postgresql
try http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL On Thu, Aug 11, 2011 at 7:32 AM, AI Rumman rumman...@gmail.com wrote: I have to convert some mysql queries to postgresql. Is there any good tools for this task? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Write performance on a large database
Hi, I have performance issues on very large database(100GB). Reading from the database is no problem, but writing(or heavy writing) is a nightmare. I have tried tuning postgresql, but that does not seem to improving the writing performance. To improve the write performance, what are my options? -- Håvard Wahl Kongsgård http://havard.security-review.net/
[GENERAL] Index on substring
Hi, my database performance badly on substring comparison between two very large tables. In postgresql 8.4 is it possible to create a index on a substring, or must I create an new field for the substring match ( and then create a new index for that field)? -- Håvard Wahl Kongsgård http://havard.security-review.net/
[GENERAL] UTF-8 and Regular expression
Hi, in 8.4 how does the regular expression functions in postgresql handle special UTF-8 characters? for example: SELECT name,substring(name from E'\\w+\\s(\\w+)$') from nodes; fails to select characters like ü ø æ å -- Håvard Wahl Kongsgård http://havard.security-review.net/
Re: [GENERAL] Performance on multiple OR conditions inside ()
This alternative was a little faster and does not return duplicated rows. It will take 41 hours on my system, but it will have to do for now. SELECT vciia_main.sitrp,vciia_main.date_time,ST_AsText(vciia_main.geom) from vciia_main, south_vietnam72 where st_within(vciia_main.geom, south_vietnam72.geom) and (st_dwithin(vciia_main.geom, (select the_geom from houses order by st_distance(vciia_main.geom, the_geom) limit 1), 500) OR st_dwithin(vciia_main.geom, (select geom from roads order by st_distance(vciia_main.geom, geom) limit 1), 500) OR st_dwithin(vciia_main.geom, (select geom from rails order by st_distance(vciia_main.geom, geom) limit 1), 500) OR st_dwithin(vciia_main.geom, (select geom from city order by st_distance(vciia_main.geom, geom) limit 1), 800)) and vciia_main.date_time is not null; -Håvard 2011/1/13 Nicklas Avén nicklas.a...@jordogskog.no No, the order is not supposed to make any difference. The planner plans the query from costs and statistics and shouldn't care about the order (I think) What you should do is to divide the big geometries and build new index. That can be done by building a grid with generate_series and cutting the geometry against that grid by using ST_Intersection between the grid and your geometry. I have not tried it myself so I don't have any query written for it. Another way that might be simplier is to use some other map than taht south Vietnam map. If you have some map with smaller regions instead the index will be more efficiency. Also use ST_Dump to tear apart all geometries as much as possible. Then build a new index and analyse for the planner to know what you have done. One thing to remember is that you in many cases will get the same geometry many times because it is within the given distance to many of your regions like ST_Intersects(Mjøsa, fylker) will give many fylke-mjøsa combinations. Regards Nicklas On Thu, 2011-01-13 at 21:51 +0100, Håvard Wahl Kongsgård wrote: Hi, so one solution is to use the most common feature first (), in this case the houses ? |What version of PostGIS are you using? 1.5.1 2011/1/13 Nicklas Avén nicklas.a...@jordogskog.no Hallo Håvard The planner is supposed to take care of that. It estimates ehat is the cheapest part of the OR statements and checks that. If true, then nothing is done with the others. Do you have spatial indexes on the geometry columns? Do you know if they are used by the planner? Even if the indexes is in place and are used this query will probably be slow because of how the spatial index works. What the spatial index does for ST_Within and ST_DWithin is to tell if the geometries has overlapping bounding boxes (or expanded bounding boxes in ST_DWithin case). If they do the index is of no more help and the rest of the calculation has to be done vertex by vertex which is costly. So, the problem is when the bounding boxes covers many geoemtries, then the part of the work that the index can help with is small. There is techniques to slice the big geometry in smaller pieces, build a new index and things will go faster. Long roads often have this problem. If you want to find all houses along a road the bounding box test will find many more houses than those close to the road (If the road is not going just north/south or east/west) I don't think it should do any difference for the planner but I would test to build the query with joins instead. What version of PostGIS are you using? Regards Nicklas Avén On Thu, 2011-01-13 at 17:47 +0100, Håvard Wahl Kongsgård wrote: Hi, I have a spatial query with multiple OR statements, for large tables it's very slow. Is it possible to skip the spatial lookup on the other conditions if first(previous) condition equal 1, and thereby increase the performance? SELECT vciia_main.sitrp,vciia_main.date_time from vciia_main, south_vietnam72, roads, rails, houses, city where st_within(vciia_main.geom, south_vietnam72.geom) and date_time is not null and (st_dwithin(vciia_main.geom, roads.geom, 500) OR st_dwithin(vciia_main.geom, rails.geom, 500) or st_dwithin(vciia_main.geom, city.geom, 800) or st_dwithin(vciia_main.geom, houses.the_geom, 500)) -- Håvard Wahl Kongsgård Peace Research Institute Oslo (PRIO) http://havard.security-review.net
[GENERAL] Performance on multiple OR conditions inside ()
Hi, I have a spatial query with multiple OR statements, for large tables it's very slow. Is it possible to skip the spatial lookup on the other conditions if first(previous) condition equal 1, and thereby increase the performance? SELECT vciia_main.sitrp,vciia_main.date_time from vciia_main, south_vietnam72, roads, rails, houses, city where st_within(vciia_main.geom, south_vietnam72.geom) and date_time is not null and (st_dwithin(vciia_main.geom, roads.geom, 500) OR st_dwithin(vciia_main.geom, rails.geom, 500) or st_dwithin(vciia_main.geom, city.geom, 800) or st_dwithin(vciia_main.geom, houses.the_geom, 500)) -- Håvard Wahl Kongsgård Peace Research Institute Oslo (PRIO) http://havard.security-review.net/
Re: [GENERAL] Performance on multiple OR conditions inside ()
Hi, so one solution is to use the most common feature first (), in this case the houses ? |What version of PostGIS are you using? 1.5.1 2011/1/13 Nicklas Avén nicklas.a...@jordogskog.no Hallo Håvard The planner is supposed to take care of that. It estimates ehat is the cheapest part of the OR statements and checks that. If true, then nothing is done with the others. Do you have spatial indexes on the geometry columns? Do you know if they are used by the planner? Even if the indexes is in place and are used this query will probably be slow because of how the spatial index works. What the spatial index does for ST_Within and ST_DWithin is to tell if the geometries has overlapping bounding boxes (or expanded bounding boxes in ST_DWithin case). If they do the index is of no more help and the rest of the calculation has to be done vertex by vertex which is costly. So, the problem is when the bounding boxes covers many geoemtries, then the part of the work that the index can help with is small. There is techniques to slice the big geometry in smaller pieces, build a new index and things will go faster. Long roads often have this problem. If you want to find all houses along a road the bounding box test will find many more houses than those close to the road (If the road is not going just north/south or east/west) I don't think it should do any difference for the planner but I would test to build the query with joins instead. What version of PostGIS are you using? Regards Nicklas Avén On Thu, 2011-01-13 at 17:47 +0100, Håvard Wahl Kongsgård wrote: Hi, I have a spatial query with multiple OR statements, for large tables it's very slow. Is it possible to skip the spatial lookup on the other conditions if first(previous) condition equal 1, and thereby increase the performance? SELECT vciia_main.sitrp,vciia_main.date_time from vciia_main, south_vietnam72, roads, rails, houses, city where st_within(vciia_main.geom, south_vietnam72.geom) and date_time is not null and (st_dwithin(vciia_main.geom, roads.geom, 500) OR st_dwithin(vciia_main.geom, rails.geom, 500) or st_dwithin(vciia_main.geom, city.geom, 800) or st_dwithin(vciia_main.geom, houses.the_geom, 500)) -- Håvard Wahl Kongsgård Peace Research Institute Oslo (PRIO) http://havard.security-review.net/ -- Håvard Wahl Kongsgård Peace Research Institute Oslo (PRIO) http://havard.security-review.net/
[GENERAL] Overriding default psql behavior | how to ignore missing fields
Hi, I am trying to insert new records from multiple SQL dumps into an existing table. My problem is that the database table does not have some of the columns used in the sql dumps. So when I try to import the dumps psql fails with: ERROR: current transaction is aborted, commands ignored until end of transaction block As there are thousands of files with multiple missing fields, manually adding every field to the table is not a option. Is it possible to overriding the default psql behavior, so that the sql session simply ignores any missing fields? -- Håvard Wahl Kongsgård http://havard.security-review.net/
[GENERAL] Overriding default psql behavior | how to ignore missing fields
Well I created the SQL files from multiple shapefiles. Used the shp2sql ( postgis 1.5) to generate the SQL dumps. On Fri, Dec 31, 2010 at 5:00 PM, Vick Khera vi...@khera.org wrote: 2010/12/31 Håvard Wahl Kongsgård haavard.kongsga...@gmail.com: Is it possible to overriding the default psql behavior, so that the sql session simply ignores any missing fields? Do you still have the original database? Re-run your table exports without the unneeded columns using COPY, then import those outputs instead. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Håvard Wahl Kongsgård Peace Research Institute Oslo (PRIO) http://havard.security-review.net/ -- Håvard Wahl Kongsgård Peace Research Institute Oslo (PRIO) http://havard.security-review.net/
