Re: How to track down inconsistent performance? - Mailing list pgsql-general
From | Ron Snyder |
---|---|
Subject | Re: How to track down inconsistent performance? |
Date | |
Msg-id | F888C30C3021D411B9DA00B0D0209BE8026E3039@cvo-exchange.cvo.roguewave.com Whole thread Raw |
In response to | How to track down inconsistent performance? (Ron Snyder <snyder@roguewave.com>) |
List | pgsql-general |
I learned last night that 'visible' is false in about 95% of the records. The products are not evenly distributed either-- sourcepro_db (the previous example) accounts for 1/2 of the records, and math (today's example) account for 3%. Here's the output of explain analyze-- these were executed less than 30 seconds apart. These are: "explain analyze" the query; do the query; "explain analyze" the query again. bash-2.05$ time psql quickview -c "explain analyze select distinct os,compiler,stdlibtype,threadlib from builds where product='math' and visible=true order by 1 asc;" NOTICE: QUERY PLAN: Unique (cost=33579.50..33664.14 rows=846 width=50) (actual time=27453.33..27478.75 rows=202 loops=1) -> Sort (cost=33579.50..33579.50 rows=8463 width=50) (actual time=27453.32..27462.24 rows=5934 loops=1) -> Index Scan using builds_visible_product on builds (cost=0.00..32868.90 rows=8463 width=50) (actual time=0.30..27266.32 rows=5934 loops=1) Total runtime: 27491.93 msec EXPLAIN real 0m27.528s user 0m0.000s sys 0m0.000s bash-2.05$ time psql quickview -c "select distinct os,compiler,stdlibtype,threadlib from builds where product='math' and visible=true order by 1 asc;" > /dev/null real 0m0.330s user 0m0.000s sys 0m0.010s bash-2.05$ time psql quickview -c "explain analyze select distinct os,compiler,stdlibtype,threadlib from builds where product='math' and visible=true order by 1 asc;" NOTICE: QUERY PLAN: Unique (cost=33579.50..33664.14 rows=846 width=50) (actual time=270.59..297.45 rows=202 loops=1) -> Sort (cost=33579.50..33579.50 rows=8463 width=50) (actual time=270.58..280.75 rows=5934 loops=1) -> Index Scan using builds_visible_product on builds (cost=0.00..32868.90 rows=8463 width=50) (actual time=0.08..90.87 rows=5934 loops=1) Total runtime: 307.74 msec EXPLAIN real 0m0.336s user 0m0.000s sys 0m0.010s Thanks for your help! -ron > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Sunday, April 28, 2002 7:59 AM > To: Ron Snyder > Cc: pgsql General List > Subject: Re: [GENERAL] How to track down inconsistent performance? > > > Ron Snyder <snyder@roguewave.com> writes: > > '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. > > Hmm, a rough guess would suggest that the query will be > retrieving about > 1/78th of the rows (unless there are statistics you haven't > mentioned that > affect that). I would think that a seqscan would be faster for this. > I would definitely think that the planner would think so --- are you > forcing enable_seqscan off? > > Since you're evidently running 7.2, I'd ask for EXPLAIN ANALYZE rather > than just EXPLAIN output. It'd be even more helpful if you are able > to catch both the fast and slow cases under EXPLAIN ANALYZE. > > regards, tom lane >
pgsql-general by date: