Re: "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(at)krogh(dot)cc> wrote:

> Hi
>
> 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.
>
> 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", "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" ON
> ( "numbers"."reference_id" = "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";
>
> QUERY
> PLAN
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ------------------------------------------------------
>
> ------------------------------------------------------------------------------
> ---------------
> Sort (cost=56246.18..56275.20 rows=11606 width=299) (actual
> time=2286.900..2287.215 rows=389 loops=1)
> Sort Key: numbers.reference_id
> -> Nested Loop Left Join (cost=388.48..55462.63 rows=11606 width=299)
> (actual time=475.071..2284.502 rows=389 loops=1)
> -> Bitmap Heap Scan on reference me (cost=388.48..23515.97
> rows=11606 width=191) (actual time=451.245..1583.966 rows=389
> loops=1)
> Recheck Cond: (sequence_id = 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,5028694,
> 5028696,5028684,5028698,5028701,5028676,5028682,5028686,5028692,5028689,304868
> 3,5305427,5305426,4970187,4970216,4970181,4970208,4970196,4970226,4970232,4970
> 201,4970191,4970222,4350307,4873618,1806537,1817367,1817432,4684270,4981822,31
> 72776,4894299,4894304,4700798,1120990,4981817,4831109,4831036,4831068,4831057,
> 4831105,4831038,4831044,4831081,4831063,4831051,4831086,4831049,4831071,483107
> 5,4831114,4831093,2635142,4660208,4660199,4912338,4660150,4662011,5307782,4894
> 286,4894292,4894296,4894309,4894313,1428388,1932290,5306082,2010148,3979647,43
> 82006,4220374,1880794,1526588,774838,1377100,969316,1796618,1121046,4662009,96
> 3535,5302610,1121105,688700,688743,688836,688763,688788,1056859,2386006,238601
> 5,2386023,4265832,4231262,4265743,5302612,1121056,1121
> 090,1121074,688659,688650}'::integer[]))
> -> Bitmap Index Scan on reference_seq_idx
> (cost=0.00..385.58 rows=11606 width=0) (actual
> time=422.691..422.691 rows=450 loops=1)
> Index Cond: (sequence_id = 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,5028694,
> 5028696,5028684,5028698,5028701,5028676,5028682,5028686,5028692,5028689,304868
> 3,5305427,5305426,4970187,4970216,4970181,4970208,4970196,4970226,4970232,4970
> 201,4970191,4970222,4350307,4873618,1806537,1817367,1817432,4684270,4981822,31
> 72776,4894299,4894304,4700798,1120990,4981817,4831109,4831036,4831068,4831057,
> 4831105,4831038,4831044,4831081,4831063,4831051,4831086,4831049,4831071,483107
> 5,4831114,4831093,2635142,4660208,4660199,4912338,4660150,4662011,5307782,4894
> 286,4894292,4894296,4894309,4894313,1428388,1932290,5306082,2010148,3979647,43
> 82006,4220374,1880794,1526588,774838,1377100,969316,1796618,1121046,4662009,96
> 3535,5302610,1121105,688700,688743,688836,688763,688788,1056859,2386006,238601
> 5,2386023,4265832,4231262,4265743,5302612,1121056,1121
> 090,1121074,688659,688650}'::integer[]))
> -> Index Scan using ecn_ref_idx on number eumbers
> (cost=0.00..2.74 rows=1 width=108) (actual time=1.794..1.795
> rows=0 loops=389)
> Index Cond: (numbers.reference_id = 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>