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

[erix]

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.