Re: Simple query, 10 million records...MySQL ten times faster

From: Benjamin Minshall
Subject: Re: Simple query, 10 million records...MySQL ten times faster
Date: ,
Msg-id: 463115D9.5040403@intellicon.biz
(view: Whole thread, Raw)
In response to: Simple query, 10 million records...MySQL ten times faster  (zardozrocks)
List: pgsql-performance


zardozrocks wrote:
>     lat_radians numeric(6,5) DEFAULT 0.00000 NOT NULL,
>     long_radians numeric(6,5) DEFAULT 0.00000 NOT NULL

Native data types such as integer or real are much faster than numeric.
  If you need 6 digits, it's better to multiply your coordinates by 10^6
and store as INTEGER.

> On my development server (dual proc/dual core Opteron 2.8 Ghz with 4GB
> ram) this query averages 1.5 seconds each time it runs after a brief
> warmup period.  In PostGreSQL it averages about 15 seconds.

What hard drive(s) and controller(s) do you have?  Please post EXPLAIN
ANALYZE output of the problem query and your postgresql.conf also.

--
Benjamin Minshall <>
Senior Developer -- Intellicon, Inc.

Attachment

pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning
From: "Andres Retzlaff"
Date:
Subject: Usage up to 50% CPU