Re: Hash join on int takes 8..114 seconds - Mailing list pgsql-performance

> You could try writing a plpgsql function which would generate the data
> set.
> Or you could use your existing data set.

Creating 3.5 mln rows using stored proc is probably slow.
Probably it would be better and faster to use some random() and
generate_series() trick.
In this case others can try it and dataset generation is faster.

> By the way, a simple way to de-bloat your big table without blocking
> would be this :
>
> - stop all inserts and updates
> - begin
> - create table new like old table
> - insert into new select * from old (order by perhaps)
> - create indexes
> - rename new into old
> - commit
>
> If this is just a reporting database where you insert a batch of new data
> every day, for instance, that's very easy to do. If it's OLTP, then, no.

Those are orders and order_products tables.
I ran vacuum full analyze verbose last night.
Now database has 4832 MB size, including 1 GB
pg_shdepend bloated indexes.
I added  max_fsm_pages=150000 and re-booted.

Query below and other queries are still too slow

set search_path to firma2,public;
explain analyze
SELECT sum(1)
   FROM dok JOIN rid USING (dokumnr)
   JOIN toode USING (toode)
   WHERE rid.toode='X05' AND dok.kuupaev>='2008-09-01'

"Aggregate  (cost=181795.13..181795.14 rows=1 width=0) (actual
time=23678.265..23678.268 rows=1 loops=1)"
"  ->  Nested Loop  (cost=73999.44..181733.74 rows=24555 width=0) (actual
time=18459.230..23598.956 rows=21476 loops=1)"
"        ->  Index Scan using toode_pkey on toode  (cost=0.00..6.01 rows=1
width=24) (actual time=0.134..0.145 rows=1 loops=1)"
"              Index Cond: ('X05'::bpchar = toode)"
"        ->  Hash Join  (cost=73999.44..181482.18 rows=24555 width=24)
(actual time=18459.076..23441.098 rows=21476 loops=1)"
"              Hash Cond: ("outer".dokumnr = "inner".dokumnr)"
"              ->  Bitmap Heap Scan on rid  (cost=4082.88..101779.03
rows=270252 width=28) (actual time=9337.782..12720.365 rows=278182 loops=1)"
"                    Recheck Cond: (toode = 'X05'::bpchar)"
"                    ->  Bitmap Index Scan on rid_toode_idx
(cost=0.00..4082.88 rows=270252 width=0) (actual time=9330.634..9330.634
rows=278183 loops=1)"
"                          Index Cond: (toode = 'X05'::bpchar)"
"              ->  Hash  (cost=69195.13..69195.13 rows=112573 width=4)
(actual time=8894.465..8894.465 rows=109890 loops=1)"
"                    ->  Bitmap Heap Scan on dok  (cost=1492.00..69195.13
rows=112573 width=4) (actual time=1618.763..8404.847 rows=109890 loops=1)"
"                          Recheck Cond: (kuupaev >= '2008-09-01'::date)"
"                          ->  Bitmap Index Scan on dok_kuupaev_idx
(cost=0.00..1492.00 rows=112573 width=0) (actual time=1612.177..1612.177
rows=110484 loops=1)"
"                                Index Cond: (kuupaev >=
'2008-09-01'::date)"
"Total runtime: 23678.790 ms"


Here is a list of untried recommendations from this thread:

1. CLUSTER rid ON rid_toode_pkey ; CLUSTER dok ON dok_kuupaev_idx
- In 8.1.4 provided form  of CLUSTER causes syntax error, no idea what
syntax to use.
Risky to try in prod server. Requires creating  randomly distributed
product_id testcase to measure
difference.

2. Change CHAR(20) product index to int index by adding update trigger.
Risky to try in prod server. Requires creating  randomly distributed
product_id testcase to measure
difference.

3. Denormalization of sale date to order_producs table by adding update
trigger.
Risky to try in prod server. Requires creating  randomly distributed
product_id testcase to measure
difference.

4. Check on the performance of the RAID: Does it leverage NCQ appropriately
when running queries in parallel ?
 No idea how.

5. Materialized views. I need date granularity so it is possible to sum only
one days sales.
http://www.pgcon.org/2008/schedule/events/69.en.html
Seems to be major appl re-write, no idea how.

Appoaches which probably does not change perfomance:

6. Upgrade to 8.4 or to 8.3.5

7. run server on standalone mode and recover 1 GB pg_shdepend bloated index.

8. tune some conf file parameters:
> work_mem = 512
I'd consider increasing this value a little - 0.5 MB seems too low to me
(but not necessarily).

> effective_cache_size= 70000
Well, your server has 2GB of RAM and usually it's recommended to set
this value to about 60-70% of your RAM, so using 540MB (25%) seems quite
low.

Data size is nearly the same as RAM size. It is unpleasant surprise that
queries take so long time.

What should I do next?


Andrus.

      1        40926 firma2.rid                                    1737 MB
      2        40595 firma2.dok                                    1632 MB
      3         1214 pg_catalog.pg_shdepend                        1235 MB
      4         1232 pg_catalog.pg_shdepend_depender_index         795 MB
      7         1233 pg_catalog.pg_shdepend_reference_index        439 MB
      8        44299 firma2.rid_toode_idx                          298 MB
      9        44286 firma2.dok_tasudok_idx                        245 MB
     10     19103791 firma2.rid_toode_pattern_idx                  202 MB
     11        44283 firma2.dok_klient_idx                         160 MB
     12        44298 firma2.rid_inpdokumnr_idx                     148 MB
     13        44297 firma2.rid_dokumnr_idx                        132 MB
     14        43573 firma2.rid_pkey                               130 MB
     17        40556 pg_toast.pg_toast_40552                       112 MB
     18        44288 firma2.dok_tasumata_idx                       103 MB
     19        44289 firma2.dok_tellimus_idx                       101 MB
     20        44284 firma2.dok_krdokumnr_idx                      101 MB
     21        44285 firma2.dok_kuupaev_idx                        94 MB
     22     19076304 firma2.rid_rtellimus_idx                      90 MB
     24        44282 firma2.dok_dokumnr_idx                        74 MB
     25        43479 firma2.dok_pkey                               74 MB
     26     18663923 firma2.dok_yksus_pattern_idx                  65 MB
     27     18801591 firma2.dok_sihtyksus_pattern_idx              64 MB
     32     18774881 firma2.dok_doktyyp                            47 MB


output from  vacuum full:


INFO:  free space map contains 14353 pages in 314 relations
DETAIL:  A total of 20000 page slots are in use (including overhead).
89664 page slots are required to track all free space.
Current limits are:  20000 page slots, 1000 relations, using 182 KB.
NOTICE:  number of page slots needed (89664) exceeds max_fsm_pages (20000)
HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a
value over 89664.

Query returned successfully with no result in 10513335 ms.