Обсуждение: Million of rows

От:
Vinicius Bernardi
Дата:

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

От:
Michael Fuhr
Дата:

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/

От:
Vinicius Bernardi
Дата:

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 <> 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/
>