Re: An unresolved performance problem. - Mailing list pgsql-performance

About the unanswered questions problem:

There seems to be a trade off between
describing a problem as minimalistically
as possible so that it gets the chance
of being read (on one hand) and giving
the full details, explain analyze,
pg_class,pg_statistic data (on the other hand),
in order to be more informational.
At the extreme cases: provide a "query slow" post
on one hand and provide the whole pg_dump
on the other.
The problem is that in the first case
"he hasnt given any real info"
and in the second case every one is avoiding
reading 10 pages of data.
I think i must have missed the "golden intersection".

Well now to the point.

The problem was dealt using a hint
from Mr Kenneth Marshall.
Setting random_page_cost = 1.9
resulted in a smaller cost calculation
for the index than the seq scan.

Now the question is:
With random_page_cost = 4 (default)
i get
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=57;

QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1669.01..1669.01 rows=1 width=0) (actual
time=258.45..258.46 rows=1 loops=1)
   ->  Seq Scan on status  (cost=0.00..1668.62 rows=158 width=0) (actual
time=171.26..258.38 rows=42 loops=1)
         Filter: ((assettable = 'vessels'::character varying) AND (appname
= 'ISM PMS'::character varying) AND (apptblname = 'items'::character
varying) AND (status = 'warn'::character varying) AND isvalid AND
(assetidval = 57))
 Total runtime: 258.52 msec
(4 rows)

dynacom=#

And with random_page_cost = 1.9, i get
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=57;

QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1650.39..1650.39 rows=1 width=0) (actual
time=18.86..18.86 rows=1 loops=1)
   ->  Index Scan using status_all on status  (cost=0.00..1650.04 rows=139
width=0) (actual time=18.26..18.77 rows=42 loops=1)
         Index Cond: ((assettable = 'vessels'::character varying) AND
(assetidval = 57) AND (appname = 'ISM PMS'::character varying) AND
(apptblname = 'items'::character varying) AND (status = 'warn'::character
varying))
         Filter: isvalid
 Total runtime: 18.94 msec
(5 rows)

dynacom=#

That is, we have a marginal decrease of the total cost
for the index scan when random_page_cost = 1.9,
whereas the "real cost" in the means of total runtime
ranges from 218 msecs (seq scan) to 19 msecs (index scan).
(is it sane?)
-----
(returning to the general -performance posting problem)
Altho a FAQ with "please do VACUUM ANALYZE before
posting to the lists" is something usefull in general,
it does not provide enuf info for the users,
at least for "corner cases" (as a fellow pgsql'er
wrote)

I think in order to stop this undesirable phaenomenon
of flooding the lists, the best way is to provide
the actual algorithms that govern the planer/optimiser,
in a form of lets say "advanced documentation".

(If there is such thing, i am sorry but i wasnt
told so by anyone.)

Otherwise there are gonna be unhappy core hackers
(having to examine each case individually)
and of course bad performing systems on the users side.

P.S.

Of course there are newbies in postgresql,
ofcourse there are people who think that
"support" is to be taken for granted,
ofcourse there are people with minimal
programming/hacking skills,
but i think the average "power user"
altho he didnt get the chance to
follow the "hard core" hacking path
in his life, he has a CompScience BSc or MSc,
and can deal with both complicated algoritmic
issues and source code reading,
and morever on the average he likes to
give and receive respect.

(not to mention that he is the person who can
"spread the word" based on strong arguments
and solid ground)

Thats my 20 drachmas.

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr