Re: Column as arrays.. more efficient than columns? - Mailing list pgsql-general

On 9/7/07, Ron Johnson <ron.l.johnson@cox.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----

> On 09/06/07 20:53, Merlin Moncure wrote:
> [snip]
> >
> > arrays are interesting and have some useful problems.  however, we
> > must first discuss the problems...first and foremost if you need to
> > read any particular item off the array you must read the entire array
> > from disk and you must right all items back to disk for writes.
>
> Reads and writes are done at the page level, so I'm not sure this is
> valid.

>>sure it is...since the denormalized record is much larger (especially
>>in array scenarios), the tuple is much larger meaning the page will
>>fill up much more quickly meaning more dead pages, more vacuuming,
>>etc.   Besides that, the server has to do some work presenting the
>>array as part of the read which is overhead.  I didn't go into a lot
>>of detail but the reasoning is sound.  Here is a quick example showing
>>the problem.

[snip]

data warehouse, so once the data has been denormalised, no need to be updated again, so i would think that merlin's tps doesn't really take into account.

Anyway.. here are some stats on the table I was working on.

denormalising the table reduced the # of rows quite a bit. and the time taken to return 2.8K rows worth of results is only ~2s on the denormalised table vs.76secs on the original table.
AFAICT, this shows me better performance in terms of read-back.

comments please since I may not necessary know what I'm doing.

BTW, I don't really get all the talk about the dead-tuples, pages filing up more quickly etc..(BTW, data below is based on denormalising  the table into column forms rather than as an array)

normalised table = 8 million
denormalised table = 328K
Join table sfoo = 3.6million
join table dbar = 1.5million
join table smallfoo = 108rows

Nested Loop  (cost=0.00..15022.75 rows=1 width=280) (actual time=0.597..1345.239 rows=2872 loops=1)
  Join Filter: ((dbar.famid)::text = (fam.famid)::text)
  ->  Nested Loop  (cost=0.00..15017.32 rows=1 width=274) (actual time=0.310..247.265 rows=2872 loops=1)
        Join Filter: ((sfoo.date_time = denorm.date_time) AND (sfoo.ttype = denorm.ttype))
        ->  Nested Loop  (cost=0.00..5767.36 rows=71 width=281) (actual time=0.246..85.985 rows=2872 loops=1)
              ->  Index Scan using idx_dbar on dbar  (cost=0.00..1175.61 rows=332 width=28) (actual time=0.154..46.172 rows=482 loops=1)
                    Index Cond: ((code)::text = 'AAA71'::text)
              ->  Index Scan using idx_denorm on denorm   (cost=0.00..13.74 rows=7 width=253) (actual time=0.017..0.055 rows=6 loops=482)
                    Index Cond: ((denorm.snum)::text = (dbar.snum)::text)
        ->  Index Scan using idx_ts_sn on sfoo  (cost=0.00..129.48 rows=46 width=37) (actual time=0.010..0.022 rows=6 loops=2872)
              Index Cond: ((sfoo.snum)::text = (norm.snum)::text)
  ->  Seq Scan on fam  (cost=0.00..4.08 rows=108 width=18) (actual time=0.004..0.169 rows=108 loops=2872)
Total runtime: 1350.234 ms

returned 2.8K rows

HashAggregate  (cost=61819.46..61819.67 rows=1 width=73) (actual time=76251.012..76586.406 rows=2872 loops=1)
  ->  Nested Loop  (cost=20.55..61819.40 rows=1 width=73) (actual time=140.007..36979.539 rows=57440 loops=1)
        Join Filter: ((dbar.famid)::text = (fam.famid::text)
        ->  Nested Loop  (cost=20.55..61813.97 rows=1 width=67) (actual time=139.585..3412.300 rows=57440 loops=1)
              Join Filter: (sfoo.ttype = norm.ttype)
              ->  Nested Loop  (cost=0.00..42351.18 rows=792 width=65) (actual time=0.117..464.893 rows=2275 loops=1)
                    ->  Index Scan using idx_dbar on bar (cost=0.00..1175.61 rows=332 width=28) (actual time=0.058..7.275 rows=482 loops=1)
                          Index Cond: ((code)::text = 'AAA71'::text)
                    ->  Index Scan using idx_sfoo on sfoo  (cost=0.00..123.45 rows=46 width=37) (actual time=0.761..0.929 rows=5 loops=482)
                          Index Cond: ((sfoo.snum)::text = (dbar.snum)::text)
              ->  Bitmap Heap Scan on norm  (cost=20.55..24.56 rows=1 width=46) (actual time=1.144..1.202 rows=25 loops=2275)
                    Recheck Cond: (((norm.snum)::text = (dbar.snum)::text) AND (sfoo.date_time = norm.date_time))
                    ->  BitmapAnd  (cost=20.55..20.55 rows=1 width=0) (actual time=0.929..0.929 rows=0 loops=2275)
                          ->  Bitmap Index Scan on idx_norm  (cost=0.00..6.70 rows=166 width=0) (actual time=0.056..0.056 rows=142 loops=2275)
                                Index Cond: ((norm.snum)::text = (dbar.snum)::text)
                          ->  Bitmap Index Scan on idx_trz_rundate  (cost=0.00..13.40 rows=604 width=0) (actual time=0.977..0.977 rows=55 loops=2021)
                                Index Cond: (sfoo.date_time = norm.date_time)
        ->  Seq Scan on fam fam_id  (cost=0.00..4.08 rows=108 width=18) (actual time=0.008..0.287 rows=108 loops=57440)
Total runtime: 76591.106 ms