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 ...


5

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 ...


5

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.


4

A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.: regress=> create unique index indexname on test USING GiST(id) ; ERROR: access method "gist" does not support unique indexes Data types ...


4

OK, so you: Dropped a bunch of constraints Did some work Attempted to add a completely unrelated random constraint and that failed You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint. You ...


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

I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to ...


3

As Craig explained very well, you cannot create a unique index on the type raster without all the necessary operators. Your second best bet to enforce uniqueness is to create a functional index on the text representation: CREATE UNIQUE INDEX us_tmin_enforce_scalex_rast ON chp05.us_tmin (cast(rast as text)) Note that this cannot be implemented as ...


3

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 = ...


3

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since: access_type ... is null in 90% of cases that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other ...


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

UPDATE mytbl SET col2=myfn(col1) should do the trick


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

Adam, First that is old document -- new one is here: http://postgis.net/docs/AddGeometryColumn.html Though what they say in both is the same. The using that function is only need for 1.5 and below. 2.0 we encourage you to just create the table normally. something like CREATE TABLE test(gid serial primary key, geom geometry(POLYGON, 4326) ); pg_dump ...


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 ...


2

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input: SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need FROM addresses a JOIN cafes c ON c.id = a.cafe_id , (SELECT lonlat FROM addresses WHERE ...


1

Seems you are running in a weakness of the query planner: The best index is sometimes not used for joining tables. Had a similar problem here: Algorithm for finding the longest prefix (Chapter "Failed attempt with text_pattern_ops") In Postgres 9.3 You could try this version with LEFT JOIN LATERAL: SELECT * FROM ( SELECT coord 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

As we can see here all the required packages are rightly updated in the official repository. To solve the problem you can find a good start point here.


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

Just a followup: I just checked it and now there is a package called postgresql-9.2-postgis-2.0. The problem is finally gone.


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

You should install PostGIS, GEOS, Proj and maybe other components first. If you install PostGIS successfully, the postgis.sql, postgis_comments.sql, spatial_ref_sys.sql will locate the $PGHOME/share/contrib directory.


1

You must install the PostGIS functions, data types, etc. before you can use PostGIS. This can be found in these three SQL-files. Set the correct path to the files and execute them in PostgreSQL by using psql.


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

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.



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