pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Million of rows
3+ messages / 2 participants
[nested] [flat]

* Million of rows
@ 2005-03-29 18:33 Vinicius Bernardi <vinicius.bernardi@gmail.com>
  2005-03-29 19:08 ` Re: Million of rows Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Vinicius Bernardi @ 2005-03-29 18:33 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Million of rows
  2005-03-29 18:33 Million of rows Vinicius Bernardi <vinicius.bernardi@gmail.com>
@ 2005-03-29 19:08 ` Michael Fuhr <mike@fuhr.org>
  2005-03-29 21:10   ` Re: Million of rows Vinicius Bernardi <vinicius.bernardi@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Fuhr @ 2005-03-29 19:08 UTC (permalink / raw)
  To: Vinicius Bernardi <vinicius.bernardi@gmail.com>; +Cc: pgsql-performance

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/



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Million of rows
  2005-03-29 18:33 Million of rows Vinicius Bernardi <vinicius.bernardi@gmail.com>
  2005-03-29 19:08 ` Re: Million of rows Michael Fuhr <mike@fuhr.org>
@ 2005-03-29 21:10   ` Vinicius Bernardi <vinicius.bernardi@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Vinicius Bernardi @ 2005-03-29 21:10 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-03-29 21:10 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-29 18:33 Million of rows Vinicius Bernardi <vinicius.bernardi@gmail.com>
2005-03-29 19:08 ` Michael Fuhr <mike@fuhr.org>
2005-03-29 21:10   ` Vinicius Bernardi <vinicius.bernardi@gmail.com>

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