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

That's interesting. I've never cared much for the limited LIKE operator. Now when you prepare this:

 select * from azjunk6 where txt ~ ?
[download]

Will it use the index when you execute the statement (e.g. with '^car[sz]')? My guess would be 'yes', and this might be the direction the OP should go.

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

Replies are listed 'Best First'.
Re^8: same query, different execution, different performance (substr) by erix (Prior) on Feb 14, 2012 at 21:47 UTC
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 [download] In this case, the plan is the same whether pg_server_prepare be 1 or 0.	[reply] [d/l]

Replies are listed 'Best First'.

Re^8: same query, different execution, different performance (substr)
by erix (Prior) on Feb 14, 2012 at 21:47 UTC

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
[download]

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

[reply]
[d/l]