Re: How to track down inconsistent performance?

Well, perhaps I'm on the right track--
quickview=> select indexrelname,idx_blks_read,idx_blks_hit from
pg_statio_user_indexes where indexrelname='builds_visible_product';
indexrelname | idx_blks_read | idx_blks_hit
------------------------+---------------+--------------
builds_visible_product | 625760 | 522572
(1 row)

Based on the numbers that the other indices report, I'm guessing that
idx_blks_read is "cache misses"-- if so, it's missing over 50% of the time.
If this is in fact what's causing the problems, then maybe there's a way to
reduce the size of the index?

And the index definition:
quickview=> select * from pg_indexes where
indexname='builds_visible_product';
tablename | indexname |
indexdef
-----------+------------------------+---------------------------------------
---------------------------------------
builds | builds_visible_product | CREATE INDEX builds_visible_product ON
builds USING btree (visible, product)
(1 row)

'visible' is a boolean, 'product' is a varchar(30), and there are about 210K
records in the builds table. (I don't know if it's relevant, but there are
about 39 distinct product values.

-ron

> -----Original Message-----
> From: Ron Snyder [mailto:snyder(at)roguewave(dot)com]
> Sent: Saturday, April 27, 2002 5:01 PM
> To: pgsql General List
> Subject: [GENERAL] How to track down inconsistent performance?
>
>
> We've got some queries that occasionally experience long run
> times (> 1
> minute), and sometimes they're very quick (< 1 second). Our
> theory is that
> when the queries are fast it's because all the "right stuff"
> is already in
> memory, but we don't know how to actually prove that. (We think we've
> successfully ruled out disk contention -- iostat shows disk
> activity during
> these queries, but nothing that's excessive.)
>
> We've turned on statistics collection-- does anybody have any
> hints about
> what things we should specifically be looking for?
>
> Here's what explain says:
> bash-2.05$ time psql quickview -c "explain select distinct
> os,compiler,stdlibtype,threadlib from builds where
> product='sourcepro_db'
> and visible=true order by 1 asc;"
> NOTICE: QUERY PLAN:
>
> Unique (cost=197093.74..197588.02 rows=4943 width=50)
> -> Sort (cost=197093.74..197093.74 rows=49428 width=50)
> -> Index Scan using builds_visible_product on builds
> (cost=0.00..192225.34 rows=49428 width=50)
>
> quickview=> \d builds
> Table "builds"
> Column | Type | Modifiers
> -------------------+--------------------------+-----------
> id | integer | not null
> visible | boolean |
> state | character(1) |
> evaluated | boolean |
> product | character varying(30) |
> compiler | character varying(30) |
> os | character varying(30) |
> stdlibtype | character varying(30) |
> linktype | character varying(30) |
> threadlib | character varying(30) |
> exportlevel | character varying(30) |
> usermode | character varying(30) |
> postbuildclean | character varying(30) |
> prebuildclean | character varying(30) |
> submitted | timestamp with time zone |
> started | timestamp with time zone |
> finished | timestamp with time zone |
> machine | character varying(100) |
> errors | integer |
> warnings | integer |
> testsattempted | integer |
> testspassed | integer |
> testsfailed | integer |
> examplesattempted | integer |
> examplespassed | integer |
> examplesfailed | integer |
> ping | timestamp with time zone |
> start_count | integer |
> user1 | character varying(50) |
> user2 | character varying(50) |
> user3 | character varying(50) |
> user4 | character varying(50) |
> user5 | character varying(50) |
> user6 | character varying(50) |
> debug | character varying(30) |
> Indexes: builds_compiler,
> builds_compiler_finished,
> builds_compiler_state,
> builds_compiler_submitted,
> builds_machine,
> builds_machine_finished,
> builds_machine_state,
> builds_machine_submitted,
> builds_os,
> builds_os_finished,
> builds_os_state,
> builds_os_submitted,
> builds_ping_finished,
> builds_pr_os_comp_std_thr_u1,
> builds_product,
> builds_product_finished,
> builds_product_state,
> builds_product_submitted,
> builds_started,
> builds_state,
> builds_visible_finished_product,
> builds_visible_product
> Primary key: builds_pkey
> Triggers: RI_ConstraintTrigger_25192277,
> RI_ConstraintTrigger_25192279
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
http://archives.postgresql.org