At 09:28 AM 22/06/2008, you wrote:

>I am looking for the fastest way to get an inventory at any date.
>For example: people living in London at 23 May 1930
>
>My table is simple, it is a person table with millions of records. The
>columns:
>
>-BirthDate
>-DeathDate
>-(Other fields like country, city, ...)
>
>
>The usual question is to calculate the inventory in any date. I use a
>basic sql statement:
>
>select count(*) from person where BirthDate<= :EndDate and (DeathDate>
>:EndDate or DeathDate is null)

Is it the case that you only want to COUNT the matching rows?

>This SQL statement works well but it is very slow because it must read
>all records, or at least, from the BirthDate to the current date if
>the table is indexed by BirthDate.
>
>It can take a lot of time, from 30 seconds to some minutes depending
>on computer.

If you actually want to return the *data* (rather than the COUNT) then testing the theory by using SELECT COUNT(*) is not a useful test. Obtaining a count by this method is intrinsically slow.

>Another idea is to create an auxiliary table and to maintain the
>inventory for each day, but it is not ok because we need to filter the
>result for example by city (i.e All people living in London at 23 May
>1930). It would imply to have many breakdowns in this temporary table.

This idea has no merit.

>My question is if somebody has had a similar scenario and how it has
>been solved.

Let's be clear about what you want to retrieve. If getting a count is your objective then a) create indexes on birthdate, deathdate and enddate and b) write a stored procedure to perform the counting and return your result. If you confirm that you are interested only in counting records, I'll give you some easy sample PSQL. ;-)

./heLen