Обсуждение: Million of rows
Hi everybody... I'm new hear, and I will try to explain my problem, and maybe I can get a help... I'm writing a software for 3 years, and this software has the position GPS from vehicles, and other informations. My problem starts when I had to store all the data about the vehicles, about 1 or 2 months. Actually I had a table called DADO_LIDO, that I write all information and the primary key is DATA (GPS DAY+HOUR) and the VEHICLE IDENTIFY. Each vehicle trasmit 1 position by 30 seconds, so I have something like 2000 rows per vehicle/day. I already has 2 clients one with 4000 vehicles, and the other with 500 vehicles. My application was made in delphi using ZEOS that's permit me testing in mysql and postgres. I allready has the two databases. But now the problem starts when I has to select data from this vehicles about the history ( I store only 2 months ) something like 40 or 50 millions of data about 500 vehicles. Using the keys VEHICLE_ID and GPS_TIME, the perfomance is very low... I need some ideas for a better perfomance in this table using selects by PERIOD / VEHICLE PERIOD / VEHICLES PERIOD / VEHICLE / ( a bit test in 3 integer columns using logical operators ) Thanks for any help Vinicius Marques De Bernardi
On Tue, Mar 29, 2005 at 03:33:24PM -0300, Vinicius Bernardi wrote: > > But now the problem starts when I has to select data from this > vehicles about the history ( I store only 2 months ) something like 40 > or 50 millions of data about 500 vehicles. > > Using the keys VEHICLE_ID and GPS_TIME, the perfomance is very low... Please post an example query and the EXPLAIN ANALYZE output. The table definition might be useful too. > I need some ideas for a better perfomance in this table Do you have indexes where you need them? Do you cluster on any of the indexes? Do you VACUUM and ANALYZE the database regularly? Have you investigated whether you need to increase the statistics on any columns? Have you tuned postgresql.conf? What version of PostgreSQL are you using? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
At now I have this system runing in a mysql, all the examples I have are in mysql, but the biggest client that will start now, we will use PostgreSQL, so I need a way to do those questions in postgres... Ideas like TABLESPACES or anothe things... Just looking for start ideas... Thanks Vinicius Marques De Bernardi On Tue, 29 Mar 2005 12:08:15 -0700, Michael Fuhr <mike@fuhr.org> wrote: > On Tue, Mar 29, 2005 at 03:33:24PM -0300, Vinicius Bernardi wrote: > > > > But now the problem starts when I has to select data from this > > vehicles about the history ( I store only 2 months ) something like 40 > > or 50 millions of data about 500 vehicles. > > > > Using the keys VEHICLE_ID and GPS_TIME, the perfomance is very low... > > Please post an example query and the EXPLAIN ANALYZE output. The > table definition might be useful too. > > > I need some ideas for a better perfomance in this table > > Do you have indexes where you need them? Do you cluster on any of > the indexes? Do you VACUUM and ANALYZE the database regularly? > Have you investigated whether you need to increase the statistics > on any columns? Have you tuned postgresql.conf? What version of > PostgreSQL are you using? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >