Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^8: same query, different execution, different performance (substr)

by erix (Vicar)
on Feb 14, 2012 at 21:47 UTC ( #953777=note: print w/ replies, xml ) Need Help??


in reply to Re^7: same query, different execution, different performance (substr)
in thread same query, different execution, different performance

Yes, with my table there seems to be no problem, the value of pg_server_prepare makes no difference for this data here. But tye is right, with certain data you need to disable it (i.e., { pg_server_prepare => 0} ).

perl -MDBI -e 'my$d=DBI->connect("dbi:Pg:",undef,undef,{pg_server_prep +are => 0 }); my $p = "^car[sz]"; my $sql = "explain analyze select * from azjunk6 where txt ~ ?"; my $s = $d->prepare($sql) or die "ouch1"; my $r = $s->execute($p) or die "ouch2"; while (my$r=$s->fetchrow_arrayref) { print $r->[0], "\n"; }' -- output: Index Only Scan using azjunk6_text_pattern_ops_idx on azjunk6 (cost=0 +.00..13.85 rows=100 width=81) (actual time=0.485..0.659 rows=6 loops= +1) Index Cond: ((txt ~>=~ 'car'::text) AND (txt ~<~ 'cas'::text)) Filter: (txt ~ '^car[sz]'::text) Rows Removed by Filter: 35 Heap Fetches: 41 Total runtime: 0.741 ms

In this case, the plan is the same whether pg_server_prepare be 1 or 0.


Comment on Re^8: same query, different execution, different performance (substr)
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://953777]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (8)
As of 2015-07-31 08:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (276 votes), past polls