Dear Gurus,
I have a query discussed here earlier that suffers heavily from "lack of
view flattening" in v7.3. Following Tom's guidance, I made a conclusion to
that thread
(http://archives.postgresql.org/pgsql-performance/2003-05/msg00215.php)
and asked it to be confirmed or fixed, but I didn't get any responses.
Here are some times, for which I'd like to get some response.
Old machine is New machine is
* PIII 800, * Dual Xeon 2.4,
* IDE 7200, * 5xSCSI 10000 HW RAID 5,
* psql 7.2.1, * psql 7.3.3,
* orig conf * orig and crude conf, as below.
* old: 18 sec * new: 24 sec
* new w/ vacuum full verbose analyze: 30-31 sec (!!!)
1. Are these times (18 vs 24) believable with such heavy HW change or is
there something fishy about it?
* I know multiprocessing doesn't come in view with a single query
* but cpu and hw speed should
* I know 7.3 is slower because of unflattened views
2. What may be the cause of VACUUM slowing the query?
3. Disabling any one of mergejoin, hashjoin, seqscan did no good. Disabling
sort prevented query from finishing in several minutes.
4. I have tried to crudely carve optimizer settings as below, but it changed
nothing according to this query. Any further ideas? Note that time tests
were taken in close succession (test; killall -HUP postmaster; test; ...)
If needed, I can attach query, exp-ana outputs before and after vacuum
(carved and uncarved conf file), and the vacuum log itself.
TIA,
G.
------------------------------- cut here -------------------------------
shared_bufers = 4096
sort_mem = 4096
effective_cache_size = 20000
random_page_cost = 1.5
------------------------------- cut here -------------------------------