Re: How to track down inconsistent performance? - Mailing list pgsql-general

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@roguewave.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