Re: Real-life range datasets - Mailing list pgsql-hackers

Hello,

We have a table in a postgres 8.4 database that would make use of date
ranges and exclusion constraints if they were available.  Sadly I cannot
give you the data as it is based on data we are paying for and as part
of the relevant licenses we are obliqued to not give the data to third
parties.

Basically the tables keep meta data about financial instruments on
a given day.  Thanks to corporate actions (companies merging, splitting
up, etc...) that meta data can be different from one day to the next.

One way to model such a table is:

identifier, date, payload columns...


unique index on (date, identifier)

(and in fact some of the payload columns are unique per day indices
as well).

But because there are a large number of rows per day and most don't
change this is a very wasteful representation.

Instead we use this

identifier, effective_from, effective_until, payload columns...

And we have some clever plpgsql functions that merge a days snapshot
into that representation.  That happens only a few times per day and
is currently quite slow mostly because a lot of time is spend in 
validation triggers to check that there are no overlapping entries
for effective_from,effective_until for jane_symbol and a few other
identifiers.

The most common operations are: 
 Get all or most rows of a given day 

(select ... from instruments where :date between effective_from and effective_until)

left join of the instruments (again in the normal case constrained to
one day but in same cases periods of a week or a few month)

select ... from t left join instruments on  t.jane_symbol = instruments.jane_symbol t.date between
instruments.effective_fromand t.effective_until where t.date = X   and additional constraint on the number of rows from
t

With t a huge table clustered on date with roughly 500,000 to 2,000,000 
entries per day.  The left join would work most of the time (my guess is
more than 90%).  But there are entries in t where the jane_symbol would
not be in instruments (sadly).

Current size (immediately after a cluster):
 table     toast        (all indices)   total 
| 1268 MB | 900 MB     | 693 MB        | 2861 MB

=> select min(effective_from), max(effective_from) from instruments;       min     |    max     
------------+------------2011-05-30 | 2011-12-21
(1 row)

b=> select count(*) from instruments where current_date - 1 between effective_from and effective_until ;count  
--------358741
(1 row)

I should be able to give you a table with the same characteristics as
the instruments table but bogus data by replacing all entries in the
table with random strings of the same length or something like that.
I can probably take a little bit of time during this or the next week
to generate such "fake" real world data ;-)   Is there an ftp site to
upload the gzipped pg_dump file to?

Cheers,

Bene

On 20/12/11 16:48, Alexander Korotkov wrote:
> Hackers,
> 
> For better GiST indexing of range types it's important to have real-life
> datasets for testing on. Real-life range datasets would help to proof (or
> reject) some concepts and get more realistic benchmarks. Also, it would be
> nice to know what queries you expect to run fast on that datasets. Ideally
> it should be real-life set of queries, but it also could be your
> presentation of what are typical queries  for such datasets.
> Thanks!
> 
> -----
> With best regards,
> Alexander Korotkov.