Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2DE9C2E0059 for ; Thu, 27 Mar 2008 14:37:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 09114-01 for ; Thu, 27 Mar 2008 14:37:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from exprod6og103.obsmtp.com (exprod6og103.obsmtp.com [64.18.1.185]) by postgresql.org (Postfix) with SMTP id BEADD2E0064 for ; Thu, 27 Mar 2008 14:37:29 -0300 (ADT) Received: from source ([63.240.6.3]) (using TLSv1) by exprod6ob103.postini.com ([64.18.5.12]) with SMTP; Thu, 27 Mar 2008 10:37:27 PDT Received: from D01HOST03.Mi8.com ([172.16.1.25]) by Outbound01.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 27 Mar 2008 13:39:46 -0400 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by D01HOST03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 27 Mar 2008 13:39:46 -0400 Received: from 69.181.101.67 ([69.181.101.67]) by MI8NYCMAIL08.Mi8.com ([172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ([172.16.1.134]) with Microsoft Exchange Server HTTP-DAV ; Thu, 27 Mar 2008 17:39:15 +0000 User-Agent: Microsoft-Entourage/12.1.0.080305 Date: Thu, 27 Mar 2008 10:36:55 -0700 Subject: Re: "Slow" query or just "Bad hardware"? From: Luke Lonergan To: Jesper Krogh , Message-ID: Thread-Topic: [PERFORM] "Slow" query or just "Bad hardware"? Thread-Index: AciQIod0yQM3Z06wTwiP+u8TtPlEvAADp7QZ In-Reply-To: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk> Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3289459015_503374" X-OriginalArrivalTime: 27 Mar 2008 17:39:46.0115 (UTC) FILETIME=[8C446930:01C89031] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/507 X-Sequence-Number: 29461 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3289459015_503374 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable You might try turning =B3enable_bitmapscan=B2 off, that will avoid the full index scan and creation of the bitmap. - Luke On 3/27/08 8:34 AM, "Jesper Krogh" wrote: > Hi >=20 > I have a table with around 10 million entries The webpage rendered hits > at most 200 records which are distributed well in the 10m with an average > of 2 "references" pr. entry. >=20 > Is there anyway to speed this query more up than allready. .. yes running > it subsequenctly it is blazingly fast, but with view of around 200/10m we > most > often dont hit the same query again. >=20 >=20 > # explain analyze SELECT "me"."created", "me"."created_initials", > "me"."updated", "me"."updated_initials", "me"."start_time", > "me"."end_time", "me"."notes", "me"."id", "me"."sequence_id", > "me"."database", "me"."name", "numbers"."reference_id", > "numbers"."evidence" FROM "reference" "me" LEFT JOIN "number" "numbers" O= N > ( "numbers"."reference_id" =3D "me"."id" ) WHERE ( "me"."sequence_id" IN ( > 34284, 41503, 42274, 42285, 76847, 78204, 104721, 126279, 274770, 274790, > 274809, 305346, 307383, 307411, 309691, 311362, 344930, 352530, 371033, > 371058, 507790, 517521, 517537, 517546, 526883, 558976, 4894317, 4976383, > 1676203, 4700800, 688803, 5028679, 5028694, 5028696, 5028684, 5028698, > 5028701, 5028676, 5028682, 5028686, 5028692, 5028689, 3048683, 5305427, > 5305426, 4970187, 4970216, 4970181, 4970208, 4970196, 4970226, 4970232, > 4970201, 4970191, 4970222, 4350307, 4873618, 1806537, 1817367, 1817432, > 4684270, 4981822, 3172776, 4894299, 4894304, 4700798, 1120990, 4981817, > 4831109, 4831036, 4831068, 4831057, 4831105, 4831038, 4831044, 4831081, > 4831063, 4831051, 4831086, 4831049, 4831071, 4831075, 4831114, 4831093, > 2635142, 4660208, 4660199, 4912338, 4660150, 4662011, 5307782, 4894286, > 4894292, 4894296, 4894309, 4894313, 1428388, 1932290, 5306082, 2010148, > 3979647, 4382006, 4220374, 1880794, 1526588, 774838, 1377100, 969316, > 1796618, 1121046, 4662009, 963535, 5302610, 1121105, 688700, 688743, > 688836, 688763, 688788, 1056859, 2386006, 2386015, 2386023, 4265832, > 4231262, 4265743, 5302612, 1121056, 1121090, 1121074, 688659, 688650 ) ) > ORDER BY "ecnumbers"."reference_id"; > =20 > QUERY > PLAN > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > -------------------------------------------------------------------------= ----- > ------------------------------------------------------ > =20 > -------------------------------------------------------------------------= ----- > --------------- > Sort (cost=3D56246.18..56275.20 rows=3D11606 width=3D299) (actual > time=3D2286.900..2287.215 rows=3D389 loops=3D1) > Sort Key: numbers.reference_id > -> Nested Loop Left Join (cost=3D388.48..55462.63 rows=3D11606 width=3D299= ) > (actual time=3D475.071..2284.502 rows=3D389 loops=3D1) > -> Bitmap Heap Scan on reference me (cost=3D388.48..23515.97 > rows=3D11606 width=3D191) (actual time=3D451.245..1583.966 rows=3D389 > loops=3D1) > Recheck Cond: (sequence_id =3D ANY > ('{34284,41503,42274,42285,76847,78204,104721,126279,274770,274790,274809= ,3053 > 46,307383,307411,309691,311362,344930,352530,371033,371058,507790,517521,= 51753 > 7,517546,526883,558976,4894317,4976383,1676203,4700800,688803,5028679,502= 8694, > 5028696,5028684,5028698,5028701,5028676,5028682,5028686,5028692,5028689,3= 04868 > 3,5305427,5305426,4970187,4970216,4970181,4970208,4970196,4970226,4970232= ,4970 > 201,4970191,4970222,4350307,4873618,1806537,1817367,1817432,4684270,49818= 22,31 > 72776,4894299,4894304,4700798,1120990,4981817,4831109,4831036,4831068,483= 1057, > 4831105,4831038,4831044,4831081,4831063,4831051,4831086,4831049,4831071,4= 83107 > 5,4831114,4831093,2635142,4660208,4660199,4912338,4660150,4662011,5307782= ,4894 > 286,4894292,4894296,4894309,4894313,1428388,1932290,5306082,2010148,39796= 47,43 > 82006,4220374,1880794,1526588,774838,1377100,969316,1796618,1121046,46620= 09,96 > 3535,5302610,1121105,688700,688743,688836,688763,688788,1056859,2386006,2= 38601 > 5,2386023,4265832,4231262,4265743,5302612,1121056,1121 > 090,1121074,688659,688650}'::integer[])) > -> Bitmap Index Scan on reference_seq_idx > (cost=3D0.00..385.58 rows=3D11606 width=3D0) (actual > time=3D422.691..422.691 rows=3D450 loops=3D1) > Index Cond: (sequence_id =3D ANY > ('{34284,41503,42274,42285,76847,78204,104721,126279,274770,274790,274809= ,3053 > 46,307383,307411,309691,311362,344930,352530,371033,371058,507790,517521,= 51753 > 7,517546,526883,558976,4894317,4976383,1676203,4700800,688803,5028679,502= 8694, > 5028696,5028684,5028698,5028701,5028676,5028682,5028686,5028692,5028689,3= 04868 > 3,5305427,5305426,4970187,4970216,4970181,4970208,4970196,4970226,4970232= ,4970 > 201,4970191,4970222,4350307,4873618,1806537,1817367,1817432,4684270,49818= 22,31 > 72776,4894299,4894304,4700798,1120990,4981817,4831109,4831036,4831068,483= 1057, > 4831105,4831038,4831044,4831081,4831063,4831051,4831086,4831049,4831071,4= 83107 > 5,4831114,4831093,2635142,4660208,4660199,4912338,4660150,4662011,5307782= ,4894 > 286,4894292,4894296,4894309,4894313,1428388,1932290,5306082,2010148,39796= 47,43 > 82006,4220374,1880794,1526588,774838,1377100,969316,1796618,1121046,46620= 09,96 > 3535,5302610,1121105,688700,688743,688836,688763,688788,1056859,2386006,2= 38601 > 5,2386023,4265832,4231262,4265743,5302612,1121056,1121 > 090,1121074,688659,688650}'::integer[])) > -> Index Scan using ecn_ref_idx on number eumbers > (cost=3D0.00..2.74 rows=3D1 width=3D108) (actual time=3D1.794..1.795 > rows=3D0 loops=3D389) > Index Cond: (numbers.reference_id =3D me.id) > Total runtime: 2287.701 ms > (10 rows) >=20 > .. subsequent run: 32.367ms >=20 > On a X4600 server with 32GB of ram and Equalogic iSCSI SAN attached. >=20 > Jesper >=20 >=20 > -- > Jesper Krogh >=20 >=20 > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >=20 --B_3289459015_503374 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: [PERFORM] "Slow" query or just "Bad hardware"= ;? You might try turning “enable_bitmapscan” off, that will= avoid the full index scan and creation of the bitmap.

- Luke


On 3/27/08 8:34 AM, "Jesper Krogh" <jesper@krogh.cc> wrote:=

Hi

I have a table with around 10 million entries  The webpage rendered hi= ts
at most 200 records which are distributed well in the 10m with an average of 2 "references" pr. entry.

Is there anyway to speed this query more up than allready. .. yes running it subsequenctly it is blazingly fast, but with view of around 200/10m we most
often dont hit the same query again.


# explain analyze SELECT "me"."created", "me"= ."created_initials",
"me"."updated", "me"."updated_initials&q= uot;, "me"."start_time",
"me"."end_time", "me"."notes", &quo= t;me"."id", "me"."sequence_id",
"me"."database", "me"."name", "= ;numbers"."reference_id",
"numbers"."evidence" FROM "reference" "m= e" LEFT JOIN "number" "numbers" ON
( "numbers"."reference_id" =3D "me"."id&qu= ot; ) WHERE ( "me"."sequence_id" IN (
34284, 41503, 42274, 42285, 76847, 78204, 104721, 126279, 274770, 274790, 274809, 305346, 307383, 307411, 309691, 311362, 344930, 352530, 371033,
371058, 507790, 517521, 517537, 517546, 526883, 558976, 4894317, 4976383, 1676203, 4700800, 688803, 5028679, 5028694, 5028696, 5028684, 5028698,
5028701, 5028676, 5028682, 5028686, 5028692, 5028689, 3048683, 5305427,
5305426, 4970187, 4970216, 4970181, 4970208, 4970196, 4970226, 4970232,
4970201, 4970191, 4970222, 4350307, 4873618, 1806537, 1817367, 1817432,
4684270, 4981822, 3172776, 4894299, 4894304, 4700798, 1120990, 4981817,
4831109, 4831036, 4831068, 4831057, 4831105, 4831038, 4831044, 4831081,
4831063, 4831051, 4831086, 4831049, 4831071, 4831075, 4831114, 4831093,
2635142, 4660208, 4660199, 4912338, 4660150, 4662011, 5307782, 4894286,
4894292, 4894296, 4894309, 4894313, 1428388, 1932290, 5306082, 2010148,
3979647, 4382006, 4220374, 1880794, 1526588, 774838, 1377100, 969316,
1796618, 1121046, 4662009, 963535, 5302610, 1121105, 688700, 688743,
688836, 688763, 688788, 1056859, 2386006, 2386015, 2386023, 4265832,
4231262, 4265743, 5302612, 1121056, 1121090, 1121074, 688659, 688650 ) ) ORDER BY "ecnumbers"."reference_id";
            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;            &nb= sp;            &= nbsp;            = ;    QUERY
PLAN
---------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ----------------------------------------------------------------------------= ---
 ---------------------------------------------------------------------= ------------------------
 Sort  (cost=3D56246.18..56275.20 rows=3D11606 width=3D299) (actual
time=3D2286.900..2287.215 rows=3D389 loops=3D1)
   Sort Key: numbers.reference_id
   ->  Nested Loop Left Join  (cost=3D388.48..554= 62.63 rows=3D11606 width=3D299)
(actual time=3D475.071..2284.502 rows=3D389 loops=3D1)
         ->  Bitmap He= ap Scan on reference me  (cost=3D388.48..23515.97
rows=3D11606 width=3D191) (actual time=3D451.245..1583.966 rows=3D389
loops=3D1)
            &nb= sp;  Recheck Cond: (sequence_id =3D ANY
('{34284,41503,42274,42285,76847,78204,104721,126279,274770,274790,274809,3= 05346,307383,307411,309691,311362,344930,352530,371033,371058,507790,517521,= 517537,517546,526883,558976,4894317,4976383,1676203,4700800,688803,5028679,5= 028694,5028696,5028684,5028698,5028701,5028676,5028682,5028686,5028692,50286= 89,3048683,5305427,5305426,4970187,4970216,4970181,4970208,4970196,4970226,4= 970232,4970201,4970191,4970222,4350307,4873618,1806537,1817367,1817432,46842= 70,4981822,3172776,4894299,4894304,4700798,1120990,4981817,4831109,4831036,4= 831068,4831057,4831105,4831038,4831044,4831081,4831063,4831051,4831086,48310= 49,4831071,4831075,4831114,4831093,2635142,4660208,4660199,4912338,4660150,4= 662011,5307782,4894286,4894292,4894296,4894309,4894313,1428388,1932290,53060= 82,2010148,3979647,4382006,4220374,1880794,1526588,774838,1377100,969316,179= 6618,1121046,4662009,963535,5302610,1121105,688700,688743,688836,688763,6887= 88,1056859,2386006,2386015,2386023,4265832,4231262,4265743,5302612,1121056,1= 121
 090,1121074,688659,688650}'::integer[]))
            &nb= sp;  ->  Bitmap Index Scan on reference_seq_idx
(cost=3D0.00..385.58 rows=3D11606 width=3D0) (actual
time=3D422.691..422.691 rows=3D450 loops=3D1)
            &nb= sp;        Index Cond: (sequence_id = =3D ANY
('{34284,41503,42274,42285,76847,78204,104721,126279,274770,274790,274809,3= 05346,307383,307411,309691,311362,344930,352530,371033,371058,507790,517521,= 517537,517546,526883,558976,4894317,4976383,1676203,4700800,688803,5028679,5= 028694,5028696,5028684,5028698,5028701,5028676,5028682,5028686,5028692,50286= 89,3048683,5305427,5305426,4970187,4970216,4970181,4970208,4970196,4970226,4= 970232,4970201,4970191,4970222,4350307,4873618,1806537,1817367,1817432,46842= 70,4981822,3172776,4894299,4894304,4700798,1120990,4981817,4831109,4831036,4= 831068,4831057,4831105,4831038,4831044,4831081,4831063,4831051,4831086,48310= 49,4831071,4831075,4831114,4831093,2635142,4660208,4660199,4912338,4660150,4= 662011,5307782,4894286,4894292,4894296,4894309,4894313,1428388,1932290,53060= 82,2010148,3979647,4382006,4220374,1880794,1526588,774838,1377100,969316,179= 6618,1121046,4662009,963535,5302610,1121105,688700,688743,688836,688763,6887= 88,1056859,2386006,2386015,2386023,4265832,4231262,4265743,5302612,1121056,1= 121
 090,1121074,688659,688650}'::integer[]))
         ->  Index Sca= n using ecn_ref_idx on number eumbers
(cost=3D0.00..2.74 rows=3D1 width=3D108) (actual time=3D1.794..1.795
rows=3D0 loops=3D389)
            &nb= sp;  Index Cond: (numbers.reference_id =3D me.id)
 Total runtime: 2287.701 ms
(10 rows)

.. subsequent run: 32.367ms

On a X4600 server with 32GB of ram and Equalogic iSCSI SAN attached.

Jesper


--
Jesper Krogh


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)<= BR> To make changes to your subscription:
http://www.p= ostgresql.org/mailpref/pgsql-performance

--B_3289459015_503374--