f



User-defined SQL Function has slower query in 7.3.3 than 7.1.3 postgresql server

I have this QUERY (below) that PostgreSQL 7.3.X servers run MUCH slower
than
the 7.1.3 server does. It makes sense that both servers have to do a
sequential scan over the ZIPCODE column. There are over 7,500 rows in the
LOCATIONS table.

Does anyone know what changed in the planner or optimizer? Can I change
the
postgresql.conf file to improve 7.3.3 performance?

Situation
---------
Here is the situation...

PG 7.1.3 returns QUERY in about 4 seconds. The EXPLAIN plan says it uses
the index on country.

PG 7.3.3 simply does not return QUERY. I've waited over 3 minutes. With
the extra condition WHERE STATE = 'NJ' it takes almost 5 seconds. Other
states are much worse.

QUERY
-----
SELECT ZIPCODE
FROM LOCATIONS
WHERE
COUNTRY = 'USA' AND ZIP_DIST_MI('07306', ZIPCODE) <= 25;

The function is written in C, using SPI. Given two US ZIP codes, it
returns the distance in miles. For example, it is 78 miles from Jersey
City to Philadelphia:

db=> select ZIP_DIST_MI('07306', '19130');
   zip_dist_mi   
-----------------
 78.801595557406
(1 row)

ZIP_DIST_MI() uses a geo_zipdata table to get the latitude and longitude.
Using those, it can calculate the "great circle distance" between ZIPs
with C double arithmetic. It finds the ZIPs locations with a prepared
(and saved) SPI query, which uses an index:

"select latitude, longitude from geo_zipdata where zip = $1"

FUNCTION
--------
CREATE FUNCTION ZIP_DIST_MI(TEXT, TEXT)
RETURNS DOUBLE PRECISION...

ZIP DATA TABLE
--------------
CREATE TABLE GEO_ZIPDATA (
 ZIP      VARCHAR(5) NOT NULL,
 STATE    VARCHAR(2) NOT NULL,
 CITY     VARCHAR(64) NOT NULL,
 COUNTY   VARCHAR(64) NOT NULL,
 LATITUDE FLOAT       NOT NULL,
 LONGITUDE FLOAT      NOT NULL,
 FIPS     NUMERIC(10) NOT NULL
);
CREATE INDEX GEO_ZIPDATA_ZIP_IDX ON GEO_ZIPDATA (ZIP);
0
adroffne
8/6/2003 3:10:43 PM
comp.databases.postgresql.sql 1301 articles. 0 followers. Post Follow

0 Replies
640 Views

Similar Articles

[PageSpeed] 40

Reply:

Similar Artilces:

GnuPG / PGP signed checksums for PostgreSQL 7.4.5, 7.4.4, 7.3.7, 7.3.6, 7.3.5. 7.2.5
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 This is a PGP-signed copy of the checksums for following PostgreSQL versions: 7.4.5 7.4.4 7.3.7 7.3.6 7.3.5 7.2.5 The latest copy of the checksums for these and other versions, as well as information on how to verify the files you download for yourself, can be found at: http://www.gtsm.com/postgres_sigs.html ## Created with md5sum: 97e750c8e69c208b75b6efedc5a36efb postgresql-7.4.5.tar.bz2 bffc3fe775c885489f9071e97f43ab9b postgresql-base-7.4.5.tar.bz2 548a73c898e65f901dbc06d622a2bc63 postgresql-docs-7.4.5.tar.b...

A problem during restoring database from PostgreSql 6.5.3/7.1.3 to 7.4
Hello �� I can dump data from old dababase(Postgresql 6.5.3), but new problem came out when I restore the data to postgresql 7.4. It went out during the COPY command, if I do it in such command lines: 1. pg_dump news -f pgsql-database-news.sql #in old system with postgresql 6.5.3 2. su - postgres #in new system with postgresql 7.4 3. createdb -T template0 news 4. psql news < pgsql-database-news.sql ------ ERROR: missing data for column "user_id" CONTEXT: COPY newses, line 1: ------ But if I do it in such command line, it works well : 1. pg_d...

Upgrading 7.3 to 7.3.3
We've had a 7.3 PG installation running for awhile now, and would like to upgrade to 7.3.3. The doc implies that you can do something other than dump your existing database, set up the new installation, and reload the dump, if you're upgrading within the 7.3 'line.' But after looking around for awhile, I don't see any guidance on how to do this. Is it anything more than moving the existing installation out of the way, installing the new one in the usual way, then copying the old PGDATA directory? (Instead of doing the initdb.) That would certainly save some...

MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Ser
MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services, (Enterprise), Developer Edition 64 Bit, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] (free donge)! No time limitation! CD NR 4203 MicroSoft SQL Server 2000 4in1 CD NR 9452 Microsoft SQL Server 2000 Service Pack 2 CD NR 11 298 AppDev SQL Server 2000 Accelerated Training [3 CDs] CD NR 12 000 Microsoft SQL Server 2000 Developer Edition 64 Bit CD NR 13...

MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services,
MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services, (Enterprise), Developer Edition 64 Bit, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] (free donge)! No time limitation! CD NR 4203 MicroSoft SQL Server 2000 4in1 CD NR 9452 Microsoft SQL Server 2000 Service Pack 2 CD NR 11 298 AppDev SQL Server 2000 Accelerated Training [3 CDs] CD NR 12 000 Microsoft SQL Server 2000 Developer Edition 64 Bit ...

MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services,
MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services, (Enterprise), Developer Edition 64 Bit, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] (free donge)! No time limitation! CD NR 4203 MicroSoft SQL Server 2000 4in1 CD NR 9452 Microsoft SQL Server 2000 Service Pack 2 CD NR 11 298 AppDev SQL Server 2000 Accelerated Training [3 CDs] CD NR 12 000 Microsoft SQL Server 2000 Developer Edition 64 Bit ...

MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services,
MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services, (Enterprise), Developer Edition 64 Bit, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] (free donge)! No time limitation! CD NR 4203 MicroSoft SQL Server 2000 4in1 CD NR 9452 Microsoft SQL Server 2000 Service Pack 2 CD NR 11 298 AppDev SQL Server 2000 Accelerated Training [3 CDs] CD NR 12 000 Microsoft SQL Server 2000 Developer Edition 64 Bit ...

building GCC 3.3.2 or 3.2.3 on Unixware 7.1.1
Has anybody done this successfully? I'm running into problems with the C++ library. AFAICT, the compiler is built ok (bootstrapping all succeeds), but the C++ library chokes. It seems to mostly be complaining about the 'volatile' keyword in pthread.h. I'm using binutils, btw. The ccs based build fails to bootstrap for me. Best regards, Brent Brent Eagles wrote: > Has anybody done this successfully? > > I'm running into problems with the C++ library. AFAICT, the compiler is > built ok (bootstrapping all succeeds), but the C++ library...

Problem installing postgresql 7.3.6 on Redhat 7.3
Dear group, I would like to know where I could find rpms for Redhat 7.3. I have looked at the downloads page and I see only source rpms and rpms for Redhat 9 and FC1. I did download the src rpm and when I run the rpm -ba postgresql.spec I get the following error message: configure: error: could not find function 'krb5_encrypt' required for Kerberos 5 error: Bad exit status from /var/tmp/rpm-tmp.93268 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.93268 (%build) How do I get over this. Regards, Shan. -------...

Update from 7.3.3 to 7.3.6
Hi, We are in the process of moving from PostgreSQL 7.3.3 to 7.3.6 (Red Hat). However, we are having a number of problems importing the database schema. Some of the SPs are written in TCL and it would seem that the library has changed. In the dump of the schema we have (comments removed for brevity): CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; CREATE FUNCTION pltcl_call_handler () RETURNS language_handler AS '$libdir/pltcl', 'pltcl_call_handler' LANGUAGE c; CREATE TRUSTED PROCEDURAL LANGUAGE pltcl HANDLER pltcl_call_handler; However, w...

Update from 7.3.3 to 7.3.6
Hi, We are in the process of moving from PostgreSQL 7.3.3 to 7.3.6 (Red Hat). However, we are having a number of problems importing the database schema. Some of the SPs are written in TCL and it would seem that the library has changed. In the dump of the schema we have (comments removed for brevity): CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; CREATE FUNCTION pltcl_call_handler () RETURNS language_handler AS '$libdir/pltcl', 'pltcl_call_handler' LANGUAGE c; CREATE TRUSTED PROCEDURAL LANGUAGE pltcl HANDLER pltcl_call_handler; However, w...

== DB Replication from 7.3.1 to 7.3.1 ==
# Have tried to prevent asking here but have spent many hours on this topic already # Actually, I'm a big Postgres fan, but replication on a MySQL box did work # out-of-the-box without any matters :-| I'd like to setup a "Hello, World database replication" between two 7.3.1 installations on two Linux hosts. The Master is running on port 5432, the slave is running through a Socket. I'm using DBMirror.pl on the client side which is being included in the core distribution since 7.3 at contrib/dbmirror. First of all, there seems to be a bug or feature that prevents it can...

== DB Replication from 7.3.1 to 7.3.1 ==
# Have tried to prevent asking here but have spent many hours on this topic already # Actually, I'm a big Postgres fan, but replication on a MySQL box did work # out-of-the-box without any matters :-| I'd like to setup a "Hello, World database replication" between two 7.3.1 installations on two Linux hosts. The Master is running on port 5432, the slave is running through a Socket. I'm using DBMirror.pl on the client side which is being included in the core distribution since 7.3 at contrib/dbmirror. First of all, there seems to be a bug or feature that prevents it can...

Problem installing matplotlib 1.3.1 with Python 2.7.6 and 3.3.3 (release candidate 1)
Hello, I tried to install matplotlib 1.3.1 on the release candidates of Python 2.7.6 and 3.3.3. I am on Mac OS X 10.6.8. Although the installation gave no problems, there is a problem with Tcl/Tk. The new Pythons have their own embedded Tcl/Tk, but when installing matplotlib it links to the Frameworks version of Tcl and TK, not to the embedded version. This causes confusion when importing matplotlib.pyplot: objc[70648]: Class TKApplication is implemented in both /Library/Frameworks/Python.framework/Versions/2.7/lib/libtk8.5.dylib and /Library/Frameworks/Tk.framework/Versions/8.5/Tk....

7.3-1 to 7.3-2
Hi Guys, I am looking at the efforts that might be involved in moving our system applications from OpenVMS 7.3-1 to 7.3-2. can anyone guide on how to proceed on this??? What all changes have gone in from 7.3-1 to 7.3-2 ??? or point to some kind of documentation on it ???? I am trying to get the release notes for 7.3-2, but looks like the HP site is undergoing some kind of work or due to some reason not able to get any thing from their site. Rgds, Bhushan In article <8a3b834.0401090045.47417059@posting.google.com>, bhushann@hotmail.com (Bhushan Narkhede) writes: > Hi Guys, > ...

The data directory was initialized by PostgreSQL version 7.3, which is not compatible with this version 7.4.3.
Hi all, I have upgraded my cygwin installation and it has replaced my old 7.3 postgresql. My questions are the following: How can I use the old files with the newer version? If this is not possible, how can I migrate my database to a newer version without the old database engine? If this is not possible how can I downgrade the postgresql to an appropriate version? (The setup utility provides only the 7.4 version) Many thanks for helping me, Vilmos ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to ch...

The data directory was initialized by PostgreSQL version 7.4, which is not compatible with this version 7.3.3.
I have installed the PostgreSQL-7.4beta5 version (the only package I could get to install from source under Mac OS X Panther) and everything worked find. However, when I start postmaster and try to create database "test" I got this error. createdb: could not connect to database template1: FATAL: The data directory was initialized by PostgreSQL version 7.4, which is not compatible with this version 7.3.3. I am assuming this means template1 was created by version 7.3.3, my previous version. Is this assumption correct? How can I fix this problem? Thanks for any h...

Install Express Server 6.3.4 (8.1.7 / RedHat 7.3) problem
Hi, Having trouble installing Oracle Express Server. During the install, it stops at 57% complete while copying DSBATCH.inf. Any ideas? TIA Steve ...

settings for postgresql.conf on 7.3.4 vs 7.2.1 and 7.2.4
Dear Guru's, I have been running the 7.2.x version of Postgresql on about 25 i686 machines and I have been updating them to the 7.2.1 to 7.2.4. I just started experimenting with the Severn beta of Redhat ?10? which has rpms for the sexy 7.3.4 of Postgresql. Well, to handle the heavy load of clients on my dedicated servers running postgresql I have set certain parameters for the postgresql.conf file as well as the shared memory. For machines with 512MB of RAM i set kernel.shmmax=425000000 in the appropriate place and in postgresql.conf I set shared_buffers=48000 max_connectio...

settings for postgresql.conf on 7.3.4 vs 7.2.1 and 7.2.4
Dear Guru's, I have been running the 7.2.x version of Postgresql on about 25 i686 machines and I have been updating them to the 7.2.1 to 7.2.4. I just started experimenting with the Severn beta of Redhat ?10? which has rpms for the sexy 7.3.4 of Postgresql. Well, to handle the heavy load of clients on my dedicated servers running postgresql I have set certain parameters for the postgresql.conf file as well as the shared memory. For machines with 512MB of RAM i set kernel.shmmax=425000000 in the appropriate place and in postgresql.conf I set shared_buffers=48000 max_connectio...

PostgreSQL 7.4 runs slower than 7.3
[I sent this 24 hours ago and it hasn't shown up yet. I may have used the wrong address. If not apologies for the dup.] I had previously mentioned that queries run much different depending on whether a VACUUM ANALYZE is done or just a plain ANALYZE. At the time I was told that that couldn't be and since I could just use the one that worked better I didn't worry about it. Now I have updated my database to 7.4 and both work badly. Here is the EXPLAIN output. Marvin is running 7.3.6 and romeo is running 7.4.3. We didn't try to do an EXPLAIN ANALYZE on romeo becaus...

Postgresql 7.3 And Redhat Enterprise 3
Hello, I've installed redhat enterprise 3 on a system to use as a db server. Manually I can install and run postgresql 7.3 (the one on the extras CD). The problem is when you try to add postgres as a service, it might add it, but it will not start it. To do this, we got a postgresql script, placed it in the init.d folder with all the other service shell scripts, and then went into services, add service, and typed postgres. The specific error received is: env: /etc/init.d/postgres no directory or file found Is there some environment variable that needs to be set in some file somewhere. ...

Postgresql 7.3 And Redhat Enterprise 3
Hello, I've installed redhat enterprise 3 on a system to use as a db server. Manually I can install and run postgresql 7.3 (the one on the extras CD). The problem is when you try to add postgres as a service, it might add it, but it will not start it. To do this, we got a postgresql script, placed it in the init.d folder with all the other service shell scripts, and then went into services, add service, and typed postgres. The specific error received is: env: /etc/init.d/postgres no directory or file found Is there some environment variable that needs to be set in some file...

Postgresql 7.3 And Redhat Enterprise 3
Hello, I've installed redhat enterprise 3 on a system to use as a db server. Manually I can install and run postgresql 7.3 (the one on the extras CD). The problem is when you try to add postgres as a service, it might add it, but it will not start it. To do this, we got a postgresql script, placed it in the init.d folder with all the other service shell scripts, and then went into services, add service, and typed postgres. The specific error received is: env: /etc/init.d/postgres no directory or file found Is there some environment variable that needs to be set in some file somewhere. ...

Web resources about - User-defined SQL Function has slower query in 7.3.3 than 7.1.3 postgresql server - comp.databases.postgresql.sql

Why aren’t user-defined operators more common?
... by technophiles and answered by users at Stack Exchange , a free, community-powered network of 100+ Q&A sites . Where have all the user-defined ...

Breaking the SQL Barrier: Google BigQuery User-Defined Functions
... analytics service that uses SQL as its query interface. As part of our BigQuery 1.8 launch , we are announcing support for executing user-defined ...

Compiler Warnings: Calling the User-Defined Default Constructor from Objective-C++ Code
Interoperating between Objective-C and C++ code requires careful work as constructor calls vary, depending on which compiler you use.

AT&T User-Defined Network Cloud Reportedly Going Live in June
While regulations aren't yet in place for non-copper networks, AT&T's all-IP network will begin offering some services as soon as June, says ...

AT&T expands its user-defined network cloud program by adding Alcatel-Lucent and Fujitsu
... announces Alcatel-Lucent (Euronext Paris and NYSE: ALU) and Fujitsu Network Communications Inc. as the newest vendors to join its User-Defined ...

AT&T Expands Its User-Defined Network Cloud Program By Adding Alcatel-Lucent and Fujitsu
... announces Alcatel-Lucent (Euronext Paris and NYSE: ALU) and Fujitsu Network Communications Inc. as the newest vendors to join its User-Defined ...

Patent US6266674 - Random access information retrieval utilizing user-defined labels - Google Patents ...
Method and apparatus for storing information wherein a user defines labels which relate to the stored information and the user defines a data ...

News: PCalc adds user-defined constants with iCloud sync
TLA Systems has released an update to PCalc for iOS adding support for user-defined constants and new functions from the Mac OS X version. PCalc ...

Add user-defined constants with PCalc RPN Calculator for iPhone and iPad
... to hit the App Store and continues to be one of the leading calculators on the market. It was recently updated to introduce a huge user-requested ...

AT&T Adds Amdocs And Juniper To Its "User-Defined Network Cloud" Supplier Program
DALLAS, April 22, 2014 /PRNewswire/ AT&T Adds Amdocs And Juniper To Its 'User-Defined Network Cloud' Supplier Program.

Resources last updated: 2/15/2016 6:58:47 AM