pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance question (something to do w/ parameterized stmts?, wrong index types?)
13+ messages / 4 participants
[nested] [flat]
* performance question (something to do w/ parameterized stmts?, wrong index types?)
@ 2006-05-08 17:29 Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:51 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
0 siblings, 2 replies; 13+ messages in thread
From: Jeffrey Tenny @ 2006-05-08 17:29 UTC (permalink / raw)
To: pgsql-performance
Why does this query take so long? (PostgreSQL 8.0.3, FC4)
Hopefully I have provided enough information below.
LOG: statement: SELECT * FROM x WHERE f IN
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,\
$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63\
,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,\
$102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,\
$133,$134,$135,$136,$137,$138,$139,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150,$151,$152,$153,$154,$155,$156,$157,$158,$159,$160,$161,$162,$163,\
$164,$165,$166,$167,$168,$169,$170,$171,$172,$173,$174,$175,$176,$177,$178,$179,$180,$181,$182,$183,$184,$185,$186,$187,$188,$189,$190,$191,$192,$193,$194,\
$195,$196,$197,$198,$199,$200,$201,$202,$203,$204,$205,$206,$207,$208,$209,$210,$211,$212,$213,$214,$215,$216,$217,$218,$219,$220,$221,$222,$223,$224,$225,\
$226,$227,$228,$229,$230,$231,$232,$233,$234,$235,$236,$237,$238,$239,$240,$241,$242,$243,$244,$245,$246,$247,$248,$249,$250,$251,$252,$253,$254,$255,$256,\
$257,$258,$259,$260,$261,$262,$263,$264,$265,$266,$267,$268,$269,$270,$271,$272,$273,$274,$275,$276,$277,$278,$279,$280,$281,$282,$283,$284,$285,$286,$287,\
$288,$289,$290,$291,$292,$293,$294,$295,$296,$297,$298,$299,$300,$301,$302,$303,$304,$305,$306,$307,$308,$309,$310,$311,$312,$313,$314,$315,$316,$317,$318,\
$319,$320,$321,$322,$323,$324,$325,$326,$327,$328,$329,$330,$331,$332,$333,$334,$335,$336,$337,$338,$339,$340,$341,$342,$343,$344,$345,$346,$347,$348,$349,\
$350,$351,$352,$353,$354,$355,$356,$357,$358,$359,$360,$361,$362,$363,$364,$365,$366,$367,$368,$369,$370,$371,$372,$373,$374,$375,$376,$377,$378,$379,$380,\
$381,$382,$383,$384,$385,$386,$387,$388,$389,$390,$391,$392,$393,$394,$395,$396,$397,$398,$399,$400,$401,$402,$403,$404,$405,$406,$407,$408,$409,$410,$411,\
$412,$413,$414,$415,$416,$417,$418,$419,$420,$421,$422,$423,$424,$425,$426,$427,$428,$429,$430,$431,$432,$433,$434,$435,$436,$437,$438,$439,$440,$441,$442,\
$443,$444,$445,$446,$447,$448,$449,$450,$451,$452,$453,$454,$455,$456,$457,$458,$459,$460,$461,$462,$463,$464,$465,$466,$467,$468,$469,$470,$471,$472,$473,\
$474,$475,$476,$477,$478,$479,$480,$481,$482,$483,$484,$485,$486,$487,$488,$489,$490,$491,$492,$493,$494,$495,$496,$497,$498,$499,$500,$501,$502,$503,$504,\
$505,$506,$507,$508,$509,$510,$511,$512,$513,$514,$515,$516,$517,$518,$519,$520,$521,$522,$523,$524,$525,$526,$527,$528,$529,$530,$531,$532,$533,$534,$535,\
$536,$537,$538,$539,$540,$541,$542,$543,$544,$545,$546,$547,$548,$549,$550,$551,$552,$553,$554,$555,$556,$557,$558,$559,$560,$561,$562,$563,$564,$565,$566,\
$567,$568,$569,$570,$571,$572,$573,$574,$575,$576,$577,$578,$579,$580,$581,$582,$583,$584,$585,$586,$587,$588,$589,$590,$591,$592,$593,$594,$595,$596,$597,\
$598,$599,$600,$601,$602,$603,$604,$605,$606,$607,$608,$609,$610,$611,$612,$613,$614,$615,$616,$617,$618,$619,$620,$621,$622,$623,$624,$625,$626,$627,$628,\
$629,$630,$631,$632,$633,$634,$635,$636,$637,$638,$639,$640,$641,$642,$643,$644,$645,$646,$647,$648,$649,$650)
ORDER BY f,c
LOG: EXECUTOR STATISTICS
DETAIL: ! system usage stats:
! 10.282945 elapsed 10.234444 user 0.048992 system sec
! [25.309152 user 0.500923 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [0/10397] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/15 [291/55] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written,
buffer hit rate = 100.00%
! Local blocks: 0 read, 0 written,
buffer hit rate = 0.00%
! Direct blocks: 0 read, 0 written
Here is the table description:
Table "public.x"
Column | Type | Modifiers
--------+---------+-----------
f | integer | not null
c | integer | not null
r | integer | not null
n | integer | not null
Indexes:
"x_c_idx" btree (c)
"x_f_idx" btree (f)
"testindex2" btree (f, c)
There are only 2,369 records in the X table.
I don't understand why this query should take 10 seconds in the executor
phase, with so little data being managed, and all relevant data already
in memory. Any clues?
Maybe there are more database server debugging options I should have
tweaked, but I'm not sure what. The stuff I turned on included:
log_duration = true
log_statement = 'all'
log_parser_stats = true
log_planner_stats = true
log_executor_stats = true
(N.B. log_statement_stats = true caused the server startup failure
every time with no error message I could find, so was not deliberately set)
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true
(FYI: 10 secs is a lot only because this query is executed many times in
my application, and they're pretty much all bad, and the aggregate query
times are killing my app response).
Thanks for any tips!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized stmts?, wrong index types?)
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2006-05-08 17:38 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
1 sibling, 1 reply; 13+ messages in thread
From: Jim C. Nasby @ 2006-05-08 17:38 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: pgsql-performance
What's EXPLAIN ANALYZE show?
On Mon, May 08, 2006 at 01:29:28PM -0400, Jeffrey Tenny wrote:
> Why does this query take so long? (PostgreSQL 8.0.3, FC4)
> Hopefully I have provided enough information below.
>
> LOG: statement: SELECT * FROM x WHERE f IN
> ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,\
> $25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63\
> ,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,\
> $102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,\
> $133,$134,$135,$136,$137,$138,$139,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150,$151,$152,$153,$154,$155,$156,$157,$158,$159,$160,$161,$162,$163,\
> $164,$165,$166,$167,$168,$169,$170,$171,$172,$173,$174,$175,$176,$177,$178,$179,$180,$181,$182,$183,$184,$185,$186,$187,$188,$189,$190,$191,$192,$193,$194,\
> $195,$196,$197,$198,$199,$200,$201,$202,$203,$204,$205,$206,$207,$208,$209,$210,$211,$212,$213,$214,$215,$216,$217,$218,$219,$220,$221,$222,$223,$224,$225,\
> $226,$227,$228,$229,$230,$231,$232,$233,$234,$235,$236,$237,$238,$239,$240,$241,$242,$243,$244,$245,$246,$247,$248,$249,$250,$251,$252,$253,$254,$255,$256,\
> $257,$258,$259,$260,$261,$262,$263,$264,$265,$266,$267,$268,$269,$270,$271,$272,$273,$274,$275,$276,$277,$278,$279,$280,$281,$282,$283,$284,$285,$286,$287,\
> $288,$289,$290,$291,$292,$293,$294,$295,$296,$297,$298,$299,$300,$301,$302,$303,$304,$305,$306,$307,$308,$309,$310,$311,$312,$313,$314,$315,$316,$317,$318,\
> $319,$320,$321,$322,$323,$324,$325,$326,$327,$328,$329,$330,$331,$332,$333,$334,$335,$336,$337,$338,$339,$340,$341,$342,$343,$344,$345,$346,$347,$348,$349,\
> $350,$351,$352,$353,$354,$355,$356,$357,$358,$359,$360,$361,$362,$363,$364,$365,$366,$367,$368,$369,$370,$371,$372,$373,$374,$375,$376,$377,$378,$379,$380,\
> $381,$382,$383,$384,$385,$386,$387,$388,$389,$390,$391,$392,$393,$394,$395,$396,$397,$398,$399,$400,$401,$402,$403,$404,$405,$406,$407,$408,$409,$410,$411,\
> $412,$413,$414,$415,$416,$417,$418,$419,$420,$421,$422,$423,$424,$425,$426,$427,$428,$429,$430,$431,$432,$433,$434,$435,$436,$437,$438,$439,$440,$441,$442,\
> $443,$444,$445,$446,$447,$448,$449,$450,$451,$452,$453,$454,$455,$456,$457,$458,$459,$460,$461,$462,$463,$464,$465,$466,$467,$468,$469,$470,$471,$472,$473,\
> $474,$475,$476,$477,$478,$479,$480,$481,$482,$483,$484,$485,$486,$487,$488,$489,$490,$491,$492,$493,$494,$495,$496,$497,$498,$499,$500,$501,$502,$503,$504,\
> $505,$506,$507,$508,$509,$510,$511,$512,$513,$514,$515,$516,$517,$518,$519,$520,$521,$522,$523,$524,$525,$526,$527,$528,$529,$530,$531,$532,$533,$534,$535,\
> $536,$537,$538,$539,$540,$541,$542,$543,$544,$545,$546,$547,$548,$549,$550,$551,$552,$553,$554,$555,$556,$557,$558,$559,$560,$561,$562,$563,$564,$565,$566,\
> $567,$568,$569,$570,$571,$572,$573,$574,$575,$576,$577,$578,$579,$580,$581,$582,$583,$584,$585,$586,$587,$588,$589,$590,$591,$592,$593,$594,$595,$596,$597,\
> $598,$599,$600,$601,$602,$603,$604,$605,$606,$607,$608,$609,$610,$611,$612,$613,$614,$615,$616,$617,$618,$619,$620,$621,$622,$623,$624,$625,$626,$627,$628,\
> $629,$630,$631,$632,$633,$634,$635,$636,$637,$638,$639,$640,$641,$642,$643,$644,$645,$646,$647,$648,$649,$650)
> ORDER BY f,c
>
> LOG: EXECUTOR STATISTICS
> DETAIL: ! system usage stats:
> ! 10.282945 elapsed 10.234444 user 0.048992 system sec
> ! [25.309152 user 0.500923 sys total]
> ! 0/0 [0/0] filesystem blocks in/out
> ! 0/0 [0/10397] page faults/reclaims, 0 [0] swaps
> ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
> ! 0/15 [291/55] voluntary/involuntary context switches
> ! buffer usage stats:
> ! Shared blocks: 0 read, 0 written,
> buffer hit rate = 100.00%
> ! Local blocks: 0 read, 0 written,
> buffer hit rate = 0.00%
> ! Direct blocks: 0 read, 0 written
>
>
> Here is the table description:
>
> Table "public.x"
> Column | Type | Modifiers
> --------+---------+-----------
> f | integer | not null
> c | integer | not null
> r | integer | not null
> n | integer | not null
> Indexes:
> "x_c_idx" btree (c)
> "x_f_idx" btree (f)
> "testindex2" btree (f, c)
>
>
> There are only 2,369 records in the X table.
>
> I don't understand why this query should take 10 seconds in the executor
> phase, with so little data being managed, and all relevant data already
> in memory. Any clues?
>
> Maybe there are more database server debugging options I should have
> tweaked, but I'm not sure what. The stuff I turned on included:
>
> log_duration = true
> log_statement = 'all'
> log_parser_stats = true
> log_planner_stats = true
> log_executor_stats = true
>
> (N.B. log_statement_stats = true caused the server startup failure
> every time with no error message I could find, so was not deliberately set)
>
> stats_start_collector = true
> stats_command_string = true
> stats_block_level = true
> stats_row_level = true
> stats_reset_on_server_start = true
>
>
>
> (FYI: 10 secs is a lot only because this query is executed many times in
> my application, and they're pretty much all bad, and the aggregate query
> times are killing my app response).
>
> Thanks for any tips!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
@ 2006-05-08 19:43 ` Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Jeffrey Tenny @ 2006-05-08 19:43 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
Well, since I don't know the exact parameter values, just substituting
1-650 for $1-$650, I get:
Index Scan using testindex2 on x (cost=0.00..34964.52 rows=1503
width=16) (actual time=0.201..968.252 rows=677 loops=1)
Filter: ((f = 1) OR (f = 2) OR (f = 3) OR (f = 4) ...
So index usage is presumably good on this one.
Jim C. Nasby wrote:
> What's EXPLAIN ANALYZE show?
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2006-05-08 20:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-05-08 20:08 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> Well, since I don't know the exact parameter values, just substituting
> 1-650 for $1-$650, I get:
> Index Scan using testindex2 on x (cost=0.00..34964.52 rows=1503
> width=16) (actual time=0.201..968.252 rows=677 loops=1)
> Filter: ((f = 1) OR (f = 2) OR (f = 3) OR (f = 4) ...
> So index usage is presumably good on this one.
No, that's not a very nice plan at all --- the key thing to notice is
it says Filter: not Index Cond:. What you've actually got here is a
full-index scan over testindex2 (I guess it's doing that to achieve the
requested sort order), then computation of a 650-way boolean OR expression
for each row of the table. Ugh.
The other way of doing this would involve 650 separate index probes and
then sorting the result. Which would be pretty expensive too, but just
counting on my fingers it seems like that ought to come out at less than
the 35000 cost units for this plan. The planner evidently is coming up
with a different answer though. You might try dropping testindex2
(which I suppose is an index on (f,c)) so that it has only an index on
f to play with, and see what plan it picks and what the estimated/actual
costs are.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-08 20:33 ` Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Jeffrey Tenny @ 2006-05-08 20:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
The original set of indexes were:
Indexes:
"x_c_idx" btree (c)
"x_f_idx" btree (f)
"testindex2" btree (f, c)
I dropped the multicolumn index 'testindex2', and a new explain analyze
looks like this:
Sort (cost=35730.71..35768.28 rows=1503 width=16) (actual
time=962.555..964.467 rows=677 loops=1)
Sort Key: f, c
-> Seq Scan on x (cost=0.00..34937.60 rows=1503 width=16) (actual
time=5.449..956.594 rows=677 loops=1)
Filter: ((f = 1) OR (f = 2) OR (f = 3) ...
Turning on the server debugging again, I got roughly identical
query times with and without the two column index.
It appears to have ignored the other indexes completely.
Tom Lane wrote:
> Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
>> Well, since I don't know the exact parameter values, just substituting
>> 1-650 for $1-$650, I get:
>
>> Index Scan using testindex2 on x (cost=0.00..34964.52 rows=1503
>> width=16) (actual time=0.201..968.252 rows=677 loops=1)
>> Filter: ((f = 1) OR (f = 2) OR (f = 3) OR (f = 4) ...
>
>> So index usage is presumably good on this one.
>
> No, that's not a very nice plan at all --- the key thing to notice is
> it says Filter: not Index Cond:. What you've actually got here is a
> full-index scan over testindex2 (I guess it's doing that to achieve the
> requested sort order), then computation of a 650-way boolean OR expression
> for each row of the table. Ugh.
>
> The other way of doing this would involve 650 separate index probes and
> then sorting the result. Which would be pretty expensive too, but just
> counting on my fingers it seems like that ought to come out at less than
> the 35000 cost units for this plan. The planner evidently is coming up
> with a different answer though. You might try dropping testindex2
> (which I suppose is an index on (f,c)) so that it has only an index on
> f to play with, and see what plan it picks and what the estimated/actual
> costs are.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2006-05-08 20:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 22:11 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-05-08 20:49 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> I dropped the multicolumn index 'testindex2', and a new explain analyze
> looks like this:
> Sort (cost=35730.71..35768.28 rows=1503 width=16) (actual
> time=962.555..964.467 rows=677 loops=1)
> Sort Key: f, c
> -> Seq Scan on x (cost=0.00..34937.60 rows=1503 width=16) (actual
> time=5.449..956.594 rows=677 loops=1)
> Filter: ((f = 1) OR (f = 2) OR (f = 3) ...
> Turning on the server debugging again, I got roughly identical
> query times with and without the two column index.
That's good, actually, seeing that the planner thinks they're close to
the same speed too. Now try "set enable_seqscan = off" to see if you
can force the multi-index-scan plan to be chosen, and see how that does.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-08 22:11 ` Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 22:15 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:06 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Jeffrey Tenny @ 2006-05-08 22:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
I tried the seqscan disabling and got what sounds like the desired plan:
Sort (cost=54900.62..54940.29 rows=1587 width=16) (actual time=20.208..22.138 rows=677 loops=1)
Sort Key: f, c
-> Index Scan using x_f_idx, x_f_idx, ...
(cost=0.00..54056.96 rows=1587 width=16) (actual time=1.048..15.598 rows=677 loops=1)
Index Cond: ((f = 1) OR (f = 2) OR (f = 3) ....
I turned off the option in postgresql.conf and it did indeed improve all similar queries on that table
to have sub-second response time, down from 6/8/10 second responses. And the elapsed time for
the application action reflected this improvement.
So that begs two questions:
1) is there a way to enable that for a single query in a multi-query transaction?
2) am I opening a can of worms if I turn it off server-wide? (PROBABLY!)
I've already had to tune the server to account for the fact that
the database is easily cached in memory but the processors are slow. (PIII 550Mhz Xeons)
I've lowered the cost of random pages and raised the cost of per-row processing
as follows (where the configuration defaults are also noted):
# - Planner Cost Constants -
#JDT: default effective_cache_size = 1000 # typically 8KB each
effective_cache_size = 50000 # typically 8KB each
#JDT: default: random_page_cost = 4 # units are one sequential page fetch cost
random_page_cost = 2 # units are one sequential page fetch cost
#JDT: default: cpu_tuple_cost = 0.01 # (same)
cpu_tuple_cost = 0.10 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#JDT: default: cpu_operator_cost = 0.0025 # (same)
cpu_operator_cost = 0.025 # (same)
Any suggestion for how to fix today's query (turning seqscan off) without wrecking others is welcome, as well as whether I've
blundered on the above (which may or may not be optimal, but definitely fixed some former problem queries
on that machine).
My transactions are large multi-query serializable transactions, so it's also important that any single-query targeting optimization
not affect other queries in the same transaction.
Thanks for the help.
Tom Lane wrote:
> Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
>> I dropped the multicolumn index 'testindex2', and a new explain analyze
>> looks like this:
>
>> Sort (cost=35730.71..35768.28 rows=1503 width=16) (actual
>> time=962.555..964.467 rows=677 loops=1)
>> Sort Key: f, c
>> -> Seq Scan on x (cost=0.00..34937.60 rows=1503 width=16) (actual
>> time=5.449..956.594 rows=677 loops=1)
>> Filter: ((f = 1) OR (f = 2) OR (f = 3) ...
>
>> Turning on the server debugging again, I got roughly identical
>> query times with and without the two column index.
>
> That's good, actually, seeing that the planner thinks they're close to
> the same speed too. Now try "set enable_seqscan = off" to see if you
> can force the multi-index-scan plan to be chosen, and see how that does.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 22:11 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2006-05-08 22:15 ` Jeffrey Tenny <jeffrey.tenny@comcast.net>
1 sibling, 0 replies; 13+ messages in thread
From: Jeffrey Tenny @ 2006-05-08 22:15 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
re my question here: what would be the JDBC-proper technique,
my app is all jdbc.
Jeffrey Tenny wrote:
> 1) is there a way to enable that for a single query in a multi-query
> transaction?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 22:11 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2006-05-08 23:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 23:35 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-05-08 23:06 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> I tried the seqscan disabling and got what sounds like the desired plan:
> Sort (cost=54900.62..54940.29 rows=1587 width=16) (actual time=20.208..22.138 rows=677 loops=1)
> Sort Key: f, c
> -> Index Scan using x_f_idx, x_f_idx, ...
> (cost=0.00..54056.96 rows=1587 width=16) (actual time=1.048..15.598 rows=677 loops=1)
> Index Cond: ((f = 1) OR (f = 2) OR (f = 3) ....
Hm, vs 35000 or so estimates for the slower plans. My recommendation
would be to decrease random_page_cost to 2 or so, instead of the brute
force disable-seqscans approach.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 22:11 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:06 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-08 23:35 ` Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:37 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Jeffrey Tenny @ 2006-05-08 23:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
Tom Lane wrote:
> Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
>> I tried the seqscan disabling and got what sounds like the desired plan:
>> Sort (cost=54900.62..54940.29 rows=1587 width=16) (actual time=20.208..22.138 rows=677 loops=1)
>> Sort Key: f, c
>> -> Index Scan using x_f_idx, x_f_idx, ...
>> (cost=0.00..54056.96 rows=1587 width=16) (actual time=1.048..15.598 rows=677 loops=1)
>> Index Cond: ((f = 1) OR (f = 2) OR (f = 3) ....
>
> Hm, vs 35000 or so estimates for the slower plans. My recommendation
> would be to decrease random_page_cost to 2 or so, instead of the brute
> force disable-seqscans approach.
The server was already running with random_page_cost=2 today for all tests, because of
the mods I've made to improve other problem queries in the past (my settings noted below, and
before in another msg on this topic).
So to nail this particular query something additional is required (even lower random_page_cost?).
What's a good value for slower processors/memory and database in memory?
1? .5?
Just curious:
Has anybody ever done an exercise that generates postgresql defaults that are customized based on the
cpu, memory, architecture, bus speeds, etc?
These old PIII xeons are quite a bit different than the newer AMD chips I use for postgres,
and the tuning of the postgresql.conf parameters has been very effective in using the old xeons, but it seems like there
must be a general knowledge base of what's generically more appropriate for some types of hardware
that would give people
better initial defaults for a given platform. I know, step right up and do it :-)
Here's the postgresql defaults and actual settings I used for all tests today (from my production server):
> I've already had to tune the server to account for the fact that
> the database is easily cached in memory but the processors are slow. (PIII 550Mhz Xeons)
> I've lowered the cost of random pages and raised the cost of per-row processing
> as follows (where the configuration defaults are also noted):
>
> # - Planner Cost Constants -
>
> #JDT: default effective_cache_size = 1000 # typically 8KB each
> effective_cache_size = 50000 # typically 8KB each
> #JDT: default: random_page_cost = 4 # units are one sequential page fetch cost
> random_page_cost = 2 # units are one sequential page fetch cost
> #JDT: default: cpu_tuple_cost = 0.01 # (same)
> cpu_tuple_cost = 0.10 # (same)
> #cpu_index_tuple_cost = 0.001 # (same)
> #JDT: default: cpu_operator_cost = 0.0025 # (same)
> cpu_operator_cost = 0.025 # (same)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 22:11 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:06 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 23:35 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2006-05-08 23:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-09 00:10 ` Re: performance question (something to do w/ parameterized Klint Gore <kg@kgb.une.edu.au>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-05-08 23:37 UTC (permalink / raw)
To: Jeffrey Tenny <jeffrey.tenny@comcast.net>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> The server was already running with random_page_cost=2 today for all tests, because of
> the mods I've made to improve other problem queries in the past (my settings noted below, and
> before in another msg on this topic).
> So to nail this particular query something additional is required (even lower random_page_cost?).
> What's a good value for slower processors/memory and database in memory?
If you're pretty sure the database will always be RAM-resident, then 1.0
is the theoretically correct value.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Re: performance question (something to do w/ parameterized stmts?, wrong index types?) Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 22:11 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:06 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 23:35 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:37 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-09 00:10 ` Klint Gore <kg@kgb.une.edu.au>
0 siblings, 0 replies; 13+ messages in thread
From: Klint Gore @ 2006-05-09 00:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim C.Nasby <jnasby@pervasive.com>; pgsql-performance@postgresql.org, Jeffrey Tenny <jeffrey.tenny@comcast.net>
On Mon, 08 May 2006 19:37:37 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jeffrey Tenny <jeffrey.tenny@comcast.net> writes:
> > The server was already running with random_page_cost=2 today for all tests, because of
> > the mods I've made to improve other problem queries in the past (my settings noted below, and
> > before in another msg on this topic).
>
> > So to nail this particular query something additional is required (even lower random_page_cost?).
> > What's a good value for slower processors/memory and database in memory?
>
> If you're pretty sure the database will always be RAM-resident, then 1.0
> is the theoretically correct value.
Would it be possible to craft a set of queries on specific data that
could advise a reasonable value for random_page_cost?
What sort of data distribution and query type would be heavily dependant
on random_page_cost? i.e. randomness of the data, size of the data
compared to physical memory.
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance question (something to do w/ parameterized
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
@ 2006-05-08 19:51 ` Jeffrey Tenny <jeffrey.tenny@comcast.net>
1 sibling, 0 replies; 13+ messages in thread
From: Jeffrey Tenny @ 2006-05-08 19:51 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; pgsql-performance
Mark Lewis wrote:
> Doing a SELECT with a large list of variables inside an IN runs slowly
> on every database we've tested. We've tested mostly in Oracle and
> PostgreSQL, and both get very slow very quickly (actually Oracle refuses
> to process the query at all after it gets too many bind parameters).
>
> In our case, we have a (potentially quite large) set of external values
> that we want to look up in the database. We originally thought that
> doing a single select with a large IN clause was the way to go, but then
> we did some performance analysis on the optimal batch size (number of
> items to include per IN clause), and discovered that for most databases,
> the optimal batch size was 1. For PostgreSQL I think it was 2.
So that is for parameterized queries (the batch size?).
In my case, I was concerned about latency between app and database
server, so I try to minimize the number of queries I send to the
database server. (My app servers can be anywhere, they /should/ be
close to the database server, but there are no guarantees and I can't
control it).
The last time I tested for optimal batch size using non-parameterized
queries with same-host database and app, I got a batch size of
approximately 700 IN list elements (again, not variables in that test).
That was on postgres 7.X.Y.
Guess I'll have to try a test where I turn the parameterized statements
into regular statements.
I'm pretty sure it would be a bad idea for me to send one IN list
element at a time in all cases. Even if the query query prep was fast,
the network latency could kill my app.
>
> The moral of the story is that you're probably better off running a
> bunch of small selects than in trying to optimize things with one
> gargantuan select.
The algorithm currently tries to ensure that IN-lists of not more than
700 elements are sent to the database server, and breaks them into
multiple queries. If it has to break it into at least 3 queries, it
uses parameterized statements for the first 2+ and then a
non-parameterized statement for the last one (which may have a different
number of IN list elements than the prior batches).
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2006-05-09 00:10 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-08 17:29 performance question (something to do w/ parameterized stmts?, wrong index types?) Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 17:38 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:43 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:08 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 20:33 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 20:49 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 22:11 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 22:15 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:06 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-08 23:35 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
2006-05-08 23:37 ` Re: performance question (something to do w/ parameterized Tom Lane <tgl@sss.pgh.pa.us>
2006-05-09 00:10 ` Re: performance question (something to do w/ parameterized Klint Gore <kg@kgb.une.edu.au>
2006-05-08 19:51 ` Re: performance question (something to do w/ parameterized Jeffrey Tenny <jeffrey.tenny@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox