pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: olivier boissard <olivier.boissard@cerene.fr>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: A.Burbello <burbello3000@yahoo.com.br>
Cc: pgsql-admin@postgresql.org
Subject: Re: improve performance in a big table
Date: Fri, 14 Dec 2007 15:51:28 +0100
Message-ID: <47629870.6060906@cerene.fr> (raw)
In-Reply-To: <dcc563d10712131004m4d81b878ra6c5d7d5fb1e82e6@mail.gmail.com>
References: <222808.5599.qm@web36215.mail.mud.yahoo.com>
	<47613061.9000004@cerene.fr>
	<dcc563d10712131004m4d81b878ra6c5d7d5fb1e82e6@mail.gmail.com>

Hello,

below a query example generated by mapserver (geographic map application) :
We try here to get data received from a transceiver  between 2 dates ( 2 
days here)
------------------------------------------------
explain analyse SELECT
    mobile_stop_sequence::text,(heading*-1)::text,
    asbinary(force_collection(force_2d(geom)),'NDR'),
    OID::text from gps_frame_history
WHERE
    ((gps_date >= '2007-12-03 23:00:00')
    AND (gps_date < '2007-12-04 22:59:59')
    AND  (natural_sbox_id=1208545575)
    AND validity::integer>2)
and
    (geom
    &&
    setSRID( 'BOX3D(-7.08254579948851 41.0580019863301,12.4007108828096 
51.278592662519)'::BOX3D,find_srid('','gps_frame_history','geom'))
    );

Result of EXPLAIN ANALYSE :
---------------------------------

"Index Scan using gist_frame_history_geom on gps_frame_history  
(cost=0.00..6.04 rows=1 width=42) (actual time=98423.796..98423.796 
rows=0 loops=1)"
"  Index Cond: (geom && 
'0103000020E61000000100000005000000BBE6D4E286541CC00632ED9B6C874440BBE6D4E286541CC05C36A3ECA8A3494015A311FA29CD28405C36A3ECA8A3494015A311FA29CD28400632ED9B6C874440BBE6D4E286541CC00632ED9B6C874440'::geometry)"
"  Filter: ((gps_date >= '2007-12-03 23:00:00'::timestamp without time 
zone) AND (gps_date < '2007-12-04 22:59:59'::timestamp without time 
zone) AND (natural_sbox_id = 1208545575) AND ((validity)::integer > 2) 
AND (geom && 
'0103000020E61000000100000005000000BBE6D4E286541CC00632ED9B6C874440BBE6D4E286541CC05C36A3ECA8A3494015A311FA29CD28405C36A3ECA8A3494015A311FA29CD28400632ED9B6C874440BBE6D4E286541CC00632ED9B6C874440'::geometry))"
"Total runtime: 98424.337 ms"



table definition :
---------------

CREATE TABLE gps_frame_history
(
  natural_sbox_id integer NOT NULL,
  gps_date timestamp without time zone NOT NULL,
  badge_id character varying(256),
  gmt_creation_date timestamp without time zone NOT NULL DEFAULT now(),
  speed numeric,
  altitude numeric,
  heading numeric,
  validity character varying(256),
  sbox_input integer,
  sbox_output integer,
  geom geometry,
  mobile_stop_sequence integer,
  "index" integer DEFAULT 
nextval(('public.gps_frame_history_index_seq'::text)::regclass),
  CONSTRAINT xpkgps_frame_history PRIMARY KEY (natural_sbox_id, gps_date),
  CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 
'POINT'::text OR geom IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (srid(geom) = 4326)
)
WITH OIDS;



-- Index: gist_frame_history_geom

-- DROP INDEX gist_frame_history_geom;

CREATE INDEX gist_frame_history_geom
  ON gps_frame_history
  USING gist
  (geom);

-- Index: idx_frame_history_oid

-- DROP INDEX idx_frame_history_oid;

CREATE UNIQUE INDEX idx_frame_history_oid
  ON gps_frame_history
  USING btree
  (oid);

-- Index: idx_gps_frame_history_id_date

-- DROP INDEX idx_gps_frame_history_id_date;

CREATE INDEX idx_gps_frame_history_id_date
  ON gps_frame_history
  USING btree
  (natural_sbox_id, gps_date);




Olivier


Scott Marlowe a écrit :
> On Dec 13, 2007 7:15 AM, olivier boissard <olivier.boissard@cerene.fr> wrote:
>   
>> A.Burbello a écrit :
>>     
>>> Hi people,
>>>
>>> I have a case and I got a doubt what should be the
>>> best ways.
>>>
>>> One table has more than 150 million of rows, and I
>>> thought that could divided by state.
>>> For each row has person ID, state and other
>>> informations, but the search would be done only by
>>> person ID (number column).
>>>
>>> I can improve the query by putting index in that
>>> column, but is there any other ways?
>>>       
>> I also studies how to improve performances on big tables.
>> Like you , I don't know how to improve without index. It's the only way
>> I found .
>> I find postgresql is fast on small table but I got real performance
>> problem when increases the number of rows
>> Do anyone know if  there is specific postgresql tuning parameters in
>> .conf file for big tables ?
>>
>> max_fsm_pages ?
>> max_fsm_relations ?
>>     
>
> More often than not, the answer lies not in tuning but in rearranging
> how you think of your data and how you create indexes.
>
> If you guys post some schema and queries (with explain analyze) that
> aren't running so fast, we'll try to help.  although pgsql-perform is
> the better place to do that.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>   


-- 
Olivier BOISSARD
olivier.boissard@cerene.fr

CERENE SERVICES SA
3, rue Archimède
10600 La Chapelle-Saint-Luc - France
Tel : 33 (0)3 25 74 11 78
Fax : 33 (0)3 25 78 39 67
----------------------------
"Ce message peut contenir des informations confidentielles et/ou
protégées. Il est à l'usage exclusif de son destinataire. Toute
utilisation non autorisée peut être illicite. Si vous recevez ce
message par erreur, nous vous remercions d'en aviser immédiatement
l'expéditeur en utilisant la fonction réponse de votre gestionnaire
de courrier électronique."

"This email may contain confidential information and/or copyright
material. This email is intended for the use of the addressee only.
Any unauthorised use may be unlawful. If you receive this email by
mistake, please advise the sender immediately by using the reply
facility in your email software." 

Attachments:

olivier_boissard.vcf (text/x-vcard, 1.1K) download begin:vcard fn:Olivier Boissard n:Boissard;Olivier org;quoted-printable:CERENE SERVICES;d=C3=A9veloppement adr:;;3 rue archimede;La Chapelle Saint Luc;AUBE;10600;FRANCE email;internet:olivier.boissard@cerene.fr tel;work:+33.3.25.74.11.78 tel;fax:+33.3.25.78.39.67 note;quoted-printable:"Ce message peut contenir des informations confidentielles et/ou=0D=0A= prot=C3=A9g=C3=A9es. Il est =C3=A0 l'usage exclusif de son destinataire.= Toute=0D=0A= utilisation non autoris=C3=A9e peut =C3=AAtre illicite. Si vous recevez= ce=0D=0A= message par erreur, nous vous remercions d'en aviser imm=C3=A9diatement=0D=0A= l'exp=C3=A9diteur en utilisant la fonction r=C3=A9ponse de votre gestionn= aire=0D=0A= de courrier =C3=A9lectronique."=0D=0A= =0D=0A= "This email may contain confidential information and/or copyright=0D=0A= material. This email is intended for the use of the addressee only.=0D=0A= Any unauthorised use may be unlawful. If you receive this email by=0D=0A= mistake, please advise the sender immediately by using the reply=0D=0A= facility in your email software." url:www.cerene.fr version:2.1 end:vcard

view thread (4+ messages)

Message-ID: <47629870.6060906@cerene.fr>
Permalink:  ../47629870.6060906@cerene.fr/
Also on:    postgresql.org/message-id/47629870.6060906@cerene.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: olivier.boissard@cerene.fr, scott.marlowe@gmail.com, burbello3000@yahoo.com.br
  Subject: Re: improve performance in a big table
  In-Reply-To: <47629870.6060906@cerene.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox