From: anthony.shipman@symstream.com
To: pgsql-performance@postgresql.org
Subject: Re: strange query plan with LIMIT
Date: Wed, 8 Jun 2011 15:08:05 +1000
User-Agent: KMail/1.9.4
Cc: tv@fuzzy.cz
References: <201106071802.08272.anthony.shipman@symstream.com>
	<30c6caeda38eb66a15665d147a5db451.squirrel@sq.gransy.com>
In-Reply-To: <30c6caeda38eb66a15665d147a5db451.squirrel@sq.gransy.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201106081508.05668.anthony.shipman@symstream.com>

On Wednesday 08 June 2011 02:40, tv@fuzzy.cz wrote:
> Hi,
>
> why are you reposting this? Pavel Stehule already recommended you to run
> ANALYZE on the tdiag table - have you done that? What was the effect?

The mailing list system hiccupped and I ended up with two posts.

VACUUM ANALYZE was done, more than once.
Setting the statistics value on the diag_id column to 1000 seemed to only m=
ake=20
the query a bit slower.

>
> The stats are off - e.g. the bitmap scan says
>
> =C2=A0 =C2=A0-> =C2=A0Bitmap Heap Scan on tdiag =C2=A0(cost=3D25763.48..6=
38085.13 rows=3D1141019
> width=3D114) (actual time=3D43.232..322.441 rows=3D86530 loops=3D1)
>
> so it expects to get 1141019 rows but it gets 86530, i.e. about 7% of the
> expected number. That might be enough to cause bad plan choice and thus
> performance issues.

What seems odd to me is that the only difference between the two is the lim=
it=20
clause:

select * from tdiag where (create_time >=3D '2011-06-03
09:49:04.000000+0' and create_time < '2011-06-06 09:59:04.000000+0') order =
by=20
diag_id limit 1;

select * from tdiag where (create_time >=3D '2011-06-03
09:49:04.000000+0' and create_time < '2011-06-06 09:59:04.000000+0') order =
by=20
diag_id;

and yet the plan completely changes.

I think that I have to force the evaluation order to get a reliably fast=20
result:

begin; create temporary table tt on commit drop as
select diag_id from tdiag where create_time >=3D '2011-06-03 09:49:04.00000=
0+0'=20
     and create_time < '2011-06-06 09:59:04.000000+0';
select * from tdiag where diag_id in (select * from tt)
     order by diag_id limit 10; commit;

                                                                QUERY PLAN
=2D------------------------------------------------------------------------=
=2D-----------------------------------------------------------------
 Limit  (cost=3D3566.24..3566.27 rows=3D10 width=3D112) (actual=20
time=3D1800.699..1800.736 rows=3D10 loops=3D1)
   ->  Sort  (cost=3D3566.24..3566.74 rows=3D200 width=3D112) (actual=20
time=3D1800.694..1800.708 rows=3D10 loops=3D1)
         Sort Key: tdiag.diag_id
         Sort Method:  top-N heapsort  Memory: 18kB
         ->  Nested Loop  (cost=3D1360.00..3561.92 rows=3D200 width=3D112) =
(actual=20
time=3D269.087..1608.324 rows=3D86530 loops=3D1)
               ->  HashAggregate  (cost=3D1360.00..1362.00 rows=3D200 width=
=3D4)=20
(actual time=3D269.052..416.898 rows=3D86530 loops=3D1)
                     ->  Seq Scan on tt  (cost=3D0.00..1156.00 rows=3D81600=
=20
width=3D4) (actual time=3D0.020..120.323 rows=3D86530 loops=3D1)
               ->  Index Scan using tdiag_pkey on tdiag  (cost=3D0.00..10.9=
9=20
rows=3D1 width=3D112) (actual time=3D0.006..0.008 rows=3D1 loops=3D86530)
                     Index Cond: (tdiag.diag_id =3D tt.diag_id)
 Total runtime: 1801.290 ms

>
> And yet another recommendation - the sort is performed on disk, so give it
> more work_mem and it should be much faster (should change from "merge
> sort" to "quick sort"). Try something like work_mem=3D20MB and see if it
> does the trick.

This certainly speeds up the sorting.

>
> regards
> Tomas

=2D-=20
Anthony Shipman                 | What most people think about
Anthony.Shipman@symstream.com   | most things is mostly wrong.