Hey short trick : to avoid to use the schema name multiple time (more readable and more easy to re use).
You can use the SET search_path gpstracking_device_tracks, public; (see manual here : http://www.postgresql.org/docs/current/static/sql-set.html) Cheers, Rémi-C 2013/10/2 Carlos Eduardo Sotelo Pinto <carlos.sotelo.pi...@gmail.com> > Thanks to all > > I have fix that refactoring the function > > BEGIN > arr := regexp_split_to_array(_imeis, E'\\s+'); > RETURN QUERY > SELECT > gpstracking_device_tracks.imei, > gpstracking_device_tracks.device_id, > gpstracking_device_tracks.date_time_process, > gpstracking_device_tracks.latitude, > gpstracking_device_tracks.longitude, > gpstracking_device_tracks.course, > gpstracking_device_tracks.speed, > gpstracking_device_tracks.mileage, > gpstracking_device_tracks.gps_signal, > gpstracking_device_tracks.gsm_signal, > gpstracking_device_tracks.alarm_status, > gpstracking_device_tracks.gps_status, > gpstracking_device_tracks.vehicle_status, > gpstracking_device_tracks.alarm_over_speed, > gpstracking_device_tracks.other, > gpstracking_device_tracks.address > FROM ( > SELECT > gpstracking_device_tracks.imei, > gpstracking_device_tracks.device_id, > gpstracking_device_tracks.date_time_process, > gpstracking_device_tracks.latitude, > gpstracking_device_tracks.longitude, > gpstracking_device_tracks.course, > gpstracking_device_tracks.speed, > gpstracking_device_tracks.mileage, > gpstracking_device_tracks.gps_signal, > gpstracking_device_tracks.gsm_signal, > gpstracking_device_tracks.alarm_status, > gpstracking_device_tracks.gps_status, > gpstracking_device_tracks.vehicle_status, > gpstracking_device_tracks.alarm_over_speed, > gpstracking_device_tracks.other, > gpstracking_device_tracks.address, > ROW_NUMBER() OVER(PARTITION BY gpstracking_device_tracks.imei ORDER BY > gpstracking_device_tracks.date_time_process DESC) as rnumber > FROM gpstracking_device_tracks > WHERE gpstracking_device_tracks.imei = ANY(arr) > AND gpstracking_device_tracks.date_time_process >= date_trunc('hour', > now()) > AND gpstracking_device_tracks.date_time_process <= NOW() > ) AS gpstracking_device_tracks > WHERE gpstracking_device_tracks.rnumber = 1; > END; > > > 2013/10/2 Merlin Moncure <mmonc...@gmail.com> > >> On Mon, Sep 30, 2013 at 10:03 AM, Carlos Eduardo Sotelo Pinto >> <carlos.sotelo.pi...@gmail.com> wrote: >> > >> > I need a help on postgresql performance >> > >> > I have configurate my postgresql files for tunning my server, however >> it is >> > slow and cpu resources are highter than 120% >> > >> > I have no idea on how to solve this issue, I was trying to search more >> infor >> > on google but is not enough, I also have try autovacum sentences and >> reindex >> > db, but it continues beeing slow >> > >> > My app is a gps listener that insert more than 6000 records per minutes >> > using a tcp server developed on python twisted, where there is no >> problems, >> > the problem is when I try to follow the gps devices on a map on a >> relatime, >> > I am doing queries each 6 seconds to my database from my django app, for >> > request last position using a stored procedure, but the query get slow >> on >> > more than 50 devices and cpu start to using more than 120% of its >> resources >> > >> > Django App connect the postgres database directly, and tcp listener >> server >> > for teh devices connect database on threaded way using pgbouncer, I >> have not >> > using my django web app on pgbouncer caause I dont want to crash gps >> devices >> > connection on the pgbouncer >> > >> > I hoe you could help on get a better performance >> > >> > I am attaching my store procedure, my conf files and my cpu, memory >> > information >> > >> > **Stored procedure** >> > >> > CREATE OR REPLACE FUNCTION gps_get_live_location ( >> > _imeis varchar(8) >> > ) >> > RETURNS TABLE ( >> > imei varchar, >> > device_id integer, >> > date_time_process timestamp with time zone, >> > latitude double precision, >> > longitude double precision, >> > course smallint, >> > speed smallint, >> > mileage integer, >> > gps_signal smallint, >> > gsm_signal smallint, >> > alarm_status boolean, >> > gsm_status boolean, >> > vehicle_status boolean, >> > alarm_over_speed boolean, >> > other text, >> > address varchar >> > ) AS $func$ >> > DECLARE >> > arr varchar[]; >> > BEGIN >> > arr := regexp_split_to_array(_imeis, E'\\s+'); >> > FOR i IN 1..array_length(arr, 1) LOOP >> > RETURN QUERY >> > SELECT >> > gpstracking_device_tracks.imei, >> > gpstracking_device_tracks.device_id, >> > gpstracking_device_tracks.date_time_process, >> > gpstracking_device_tracks.latitude, >> > gpstracking_device_tracks.longitude, >> > gpstracking_device_tracks.course, >> > gpstracking_device_tracks.speed, >> > gpstracking_device_tracks.mileage, >> > gpstracking_device_tracks.gps_signal, >> > gpstracking_device_tracks.gsm_signal, >> > gpstracking_device_tracks.alarm_status, >> > gpstracking_device_tracks.gps_status, >> > gpstracking_device_tracks.vehicle_status, >> > gpstracking_device_tracks.alarm_over_speed, >> > gpstracking_device_tracks.other, >> > gpstracking_device_tracks.address >> > FROM gpstracking_device_tracks >> > WHERE gpstracking_device_tracks.imei = arr[i]::VARCHAR >> > AND gpstracking_device_tracks.date_time_process >= >> date_trunc('hour', >> > now()) >> > AND gpstracking_device_tracks.date_time_process <= NOW() >> > ORDER BY gpstracking_device_tracks.date_time_process DESC >> > LIMIT 1; >> > END LOOP; >> > RETURN; >> > END; >> > $func$ >> > LANGUAGE plpgsql VOLATILE SECURITY DEFINER; >> >> >> Why are you doing this in a loop? What's the point of the LIMIT 1? >> You can almost certainly refactor this procedure into a vanilla query. >> >> merlin >> > > > > -- > Carlos Eduardo Sotelo Pinto | http://carlossotelo.com | csotelo@twitter > GNU Linux Admin | PHP Senior Web Developer > Mobil: RPC (Claro)+51, 958194614 | Mov: +51, 959980794 > GTalk: carlos.sotelo.pi...@gmail.com | Skype: csotelop > MSN: carlos.sotelo.pi...@gmail.com | Yahoo: csotelop > GNULinux RU #379182 | GNULinux RM #277661 > GPG FP:697E FAB8 8E83 1D60 BBFB 2264 9E3D 5761 F855 4F6B >
