Tag Info

Hot answers tagged

11

This is one of those "It depends" questions. Performance depends on resources, contention, configuration, and the VM engine Uncontended VM host: If you properly resource a VM with uncontended high performance locally-attached or SAN storage, low contention for CPU resources, no memory overcommit or contention, fast dedicated network access, etc, it'll ...


6

First, use ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography instead of ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')') Per documentation: ST_MakePoint while not being OGC compliant is generally faster and more precise than ST_GeomFromText and ST_PointFromText. It is also easier to use if you ...


4

Answer to question SELECT DISTINCT ON (name, zonedistrict_id) ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname FROM zones GROUP BY gid, name, zonedistrict_id, zonestyle_id, longname ORDER BY name, zonedistrict_id, zonestyle_id; It depends on what you are actually trying to achieve and what version of Postgres you are ...


3

As a part of my work I maintain a fairly large PostgreSQL database (around 120gb on disk, several multi-million-row tables) and have collected a few tricks on how to speed up the queries. First some comments on your assumptions: Yes, order is important, but it's only the first one that is really different, the rest are second class indexes. I'm not sure ...


3

Off the top of my head, here are a few things to do, in no particular order. VACUUM FREEZE ANALYZE Add the indexes that you think are going to help. Execute as many queries as you can. Look at pg_stat_user_indexes to see which queries are being used. Look at the EXPLAIN ANALYZE plan for each query. Review ...


3

Try this: Re: could not change directory to "/root": Apparently you did "su postgres" from the root account, so you're still in root's home directory. It'd be better to do "su - postgres" to ensure you've acquired all of the postgres account's environment. Reading "man su" might help you out here.


2

Just don't leave the Database field empty in the connection dialog. When left empty, it will attempt to connect to a database named after your OS login name, which probably doesn't exist in your case. The Host field should be left empty to use a Unix domain socket connection, as hinted by the snippet of your pg_hba.conf shown in the question. When you put ...


2

First try connecting to the database as the postgres user jay@machine$ sudo su postgres postgres@machine$ psql Then create a new user, and why not create a database if you haven't already postgres=# CREATE USER jay WITH password 'donttell'; postgres=# CREATE DATABASE gis; postgres=# \q You should now be able to connect as yourself jay@machine$ psql ...


2

Start by using a current PostgreSQL version, preferably a PGDG build. See http://yum.postgresql.org. Then compare with the same hardware. There is no known significant performance difference with 64-bit vs 32-bit PostgreSQL on non-Windows platforms. Personally, I suspect that the 64-bit CentOS might have transparent huge pages enabled, which does cause ...


2

In general I have doubts about database servers on a VM. I don't doubt it is possible as per the previous answer but it strikes me as a complexity loss particularly for PostgreSQL where you could instead run several clusters on the same system without virtualization as long as you different IP addresses or ports. The thing people talk about on the pro side ...


2

Try this untangled version to update all rows of char_check: UPDATE car_check c SET fencing = ST_Contains(u.geom, ST_MakePoint(t.lat::float8, t.lng::float8)) FROM test t ,rule r JOIN ( SELECT DISTINCT ON (polyname) polyname, geom FROM unknown_table ORDER BY polyname, version DESC ) u USING (polyname) WHERE t.id = ...


2

You don't seem to join the tables you are trying to update from. I think the query will need to be converted to this: UPDATE car_check set fencing=foo.ST_Contains from ( SELECT ST_Contains(ST_AsText(p.geom), ST_GeomFromText('POINT(' || to_char(t.lat, '9999.999999') || ' ' || to_char(t.log, '9999.999999') || ')') AS ST_Contains, cc.carid, cc.cargroupid FROM ...


1

I think the thing most likely to help (if anything) would be to add product_type as a 2nd column to the gist index. But without knowing how many rows match each of the AND conditions (in isolation) for your typical/problematic queries, we can only guess. When I approach this, the first thing I do is run the query in simplified form where the WHERE clause ...


1

Sounds to me like you have PostGIS installed in your database more than once, possibly once via CREATE EXTENSION and again via a load script, or from a dump. The hint is that: PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry mentions the schema qualifier postgis.geometry for one operand, and omits it for another. I'd say ...


1

As you have absolutely no filtering clauses (no WHERE clauses or JOINs that would filter the rows from the base table) the only reason you should get zero rows from that query is that there are zero rows in the table for it to return. SELECT COUNT(*) FROM OpenStreetMapView will confirm or contradict this. If a process that should have populated the table ...


1

It isn't even clear if you should be partitioning at all. PostgreSQL's table partitioning is a bit primitive and comes with some limitations in enforcing referential integrity, etc. If your query pattern doesn't overwhelmingly favour filters on a particular field where you can benefit from constraint exclusion it might not help you much. Partitioning can ...


1

The column "row_num" doesn't exist because the logical order of processing requires the dbms to apply the WHERE clause before it evaluates the SELECT clause. The windowing function is part of the SELECT clause, so its alias isn't accessible in the same statement's WHERE clause. The FROM clause is the very first part of the statement to be evaluated. That's ...


1

Maybe this will help start you out but here is how I am thinking through this. I can't think of any case, re spherical trig where two straight lines would be closer in the middle than at the edges, unless they cross so I think you can narrow your search down to the vertices of each road plus the nearest point to that vertex on the other road. I am assuming ...


1

Turns out to have been a bug in the GEOS package-- there is an announcement on the postgis-users list. The link to the bug report is here.


1

From this listing it looks like there is a package named postgresql-9.2-postgis2. You may have to add a new apt repository to the existing ones. In this, you can find help here.



Only top voted, non community-wiki answers of a minimum length are eligible