Optimizing query?

Le mercredi 30 janvier 2013 ￜ 11:08 +0000, wolfgang@noten5.maas-noten.de
a ï¿œcrit :

Hi,

I am trying to match items from 2 tables based on a common string.
One is a big table which has one column with entries like XY123, ABC44, etc
The table has an index on that column.
The second table is, typically, much smaller

select .... from tab1, tab2 where tab1.code = tab2.code;

This works fine and fast.
Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D in the
big table and want them to match XY423, GF55 in the second table

Variants I have tried

select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z])');

Have you tried the substring function?

select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =
tab2.code

both take an enormous time. In the better case that I can subset (e.g. all candidates in table 2
share initial "AX") I get back to manageable times by adding
and tab1.code ~ '^AX'
into the recipe. Actual runtime with about a million entries in tab1 and 800 entries in tab2
is about 40 seconds.

Regards
Wolfgang Hamann

--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:

Le mercredi 30 janvier 2013 à 11:08 +0000, wolfgang@noten5.maas-noten.de
a écrit :

Hi,

I am trying to match items from 2 tables based on a common string.
One is a big table which has one column with entries like XY123, ABC44, etc
The table has an index on that column.
The second table is, typically, much smaller

select .... from tab1, tab2 where tab1.code = tab2.code;

This works fine and fast.
Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D in the
big table and want them to match XY423, GF55 in the second table

Variants I have tried

select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z])');

Have you tried the substring function?

select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =
tab2.code

both take an enormous time. In the better case that I can subset (e.g. all candidates in table 2
share initial "AX") I get back to manageable times by adding
and tab1.code ~ '^AX'
into the recipe. Actual runtime with about a million entries in tab1 and 800 entries in tab2
is about 40 seconds.

any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.

You can try use a functional index.

create index on tab2 ((substring(tab1.code from 1 for 5))

Regards

Pavel Stehule

Regards
Wolfgang Hamann

--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Le jeudi 31 janvier 2013 ￜ 09:49 +0100, Pavel Stehule a ￜcrit :

any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.

Hi Pavel,

Thank you for the correction. Since we're at it, I have a question
regarding functions in a query :

Suppose I have a query of the form

SELECT my_function(column_1), column_2
FROM my_table
GROUP BY my_function(column_1)
ORDER BY my_function(column_1);

where my_function is a user defined function.

How many times is the function computed?

--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:

Le jeudi 31 janvier 2013 à 09:49 +0100, Pavel Stehule a écrit :

any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.

Hi Pavel,

Thank you for the correction. Since we're at it, I have a question
regarding functions in a query :

Suppose I have a query of the form

SELECT my_function(column_1), column_2
FROM my_table
GROUP BY my_function(column_1)
ORDER BY my_function(column_1);

where my_function is a user defined function.

How many times is the function computed?

if function is stable or immutable, then once per row

Pavel

--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Le jeudi 31 janvier 2013 ￜ 11:06 +0100, Pavel Stehule a ￜcrit :

2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:

Suppose I have a query of the form

SELECT my_function(column_1), column_2
FROM my_table
GROUP BY my_function(column_1)
ORDER BY my_function(column_1);

where my_function is a user defined function.

How many times is the function computed?

if function is stable or immutable, then once per row

In this post (watch for line-wrap) :

/messages/by-id/CAFj8pRAdYL1-hCxH
+QSZQKHt9YnoaOiGkfX4cNc9mzUTimcs1w@mail.gmail.com

you wrote that it is usually better not to mark SQL functions (as
opposed to plpgsql functions).

So should I mark SQL functions stable/immutable if I use them in a query
like the one above, or is it unnecessary?

--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:

Le jeudi 31 janvier 2013 à 11:06 +0100, Pavel Stehule a écrit :

2013/1/31 Vincent Veyron <vv.lists@wanadoo.fr>:

Suppose I have a query of the form

SELECT my_function(column_1), column_2
FROM my_table
GROUP BY my_function(column_1)
ORDER BY my_function(column_1);

where my_function is a user defined function.

How many times is the function computed?

if function is stable or immutable, then once per row

In this post (watch for line-wrap) :

/messages/by-id/CAFj8pRAdYL1-hCxH
+QSZQKHt9YnoaOiGkfX4cNc9mzUTimcs1w@mail.gmail.com

you wrote that it is usually better not to mark SQL functions (as
opposed to plpgsql functions).

So should I mark SQL functions stable/immutable if I use them in a query
like the one above, or is it unnecessary?

It should not be marked

Regards

Pavel

--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Logiciel de gestion des contentieux juridiques et des sinistres d'assurance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavel Stehlule wrote:

Hi,

I am trying to match items from 2 tables based on a common string.
One is a big table which has one column with entries like XY123, ABC44, =

etc

The table has an index on that column.
The second table is, typically, much smaller

select .... from tab1, tab2 where tab1.code =3D tab2.code;

This works fine and fast.
Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D=

in the

big table and want them to match XY423, GF55 in the second table

Variants I have tried

select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z=

])');

Have you tried the substring function?

select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =3D
tab2.code

Hi Pavel, it was just by chance that a fixed size substring would match the
data at hand. It is more common to have a digit/letter (or vice versa) boundary
or a hyphen there

both take an enormous time. In the better case that I can subset (e.g. a=

ll candidates in table 2

share initial "AX") I get back to manageable times by adding
and tab1.code ~ '^AX'
into the recipe. Actual runtime with about a million entries in tab1 and=

800 entries in tab2

is about 40 seconds.

any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.

I see the problem since obviously every the ~ operator with a non-constant
pattern is constantly recompiling the pattern.

I wonder whether it would be possible to invent a prefix-match operator that approaches
the performance of string equality. I noted in the past (not sure whether anything
has changed in regex matching) that a constant leading part of regex would improve
performance, i.e. use an index scan to select possible candidates.

You can try use a functional index.

create index on tab2 ((substring(tab1.code from 1 for 5))

What kind of trick is that - mixing two tables into a functional index?
What would the exact syntax be for that?

Regards
Wolfgang Hamann

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello

2013/1/31 <hamann.w@t-online.de>:

Pavel Stehlule wrote:

Hi,

I am trying to match items from 2 tables based on a common string.
One is a big table which has one column with entries like XY123, ABC44, =

etc

The table has an index on that column.
The second table is, typically, much smaller

select .... from tab1, tab2 where tab1.code =3D tab2.code;

This works fine and fast.
Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D=

in the

big table and want them to match XY423, GF55 in the second table

Variants I have tried

select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z=

])');

Have you tried the substring function?

select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =3D
tab2.code

Hi Pavel, it was just by chance that a fixed size substring would match the
data at hand. It is more common to have a digit/letter (or vice versa) boundary
or a hyphen there

both take an enormous time. In the better case that I can subset (e.g. a=

ll candidates in table 2

share initial "AX") I get back to manageable times by adding
and tab1.code ~ '^AX'
into the recipe. Actual runtime with about a million entries in tab1 and=

800 entries in tab2

is about 40 seconds.

any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.

I see the problem since obviously every the ~ operator with a non-constant
pattern is constantly recompiling the pattern.

I wonder whether it would be possible to invent a prefix-match operator that approaches
the performance of string equality. I noted in the past (not sure whether anything
has changed in regex matching) that a constant leading part of regex would improve
performance, i.e. use an index scan to select possible candidates.

You can try use a functional index.

create index on tab2 ((substring(tab1.code from 1 for 5))

What kind of trick is that - mixing two tables into a functional index?

it is not possible - you can do some auxiliary table and creating
indexes over this table

but maybe https://github.com/dimitri/prefix can help

Regards

Pavel

What would the exact syntax be for that?

Regards
Wolfgang Hamann

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavel Stehule wrote:

but maybe https://github.com/dimitri/prefix can help

Hi Pavel,

thanks - this works perfect. However, it does not seem to play well
with the optimizer, so I ended up with

select all candidates into a temp table using prefix operator
apply all other conditions by joining that temp table to original ones

Regards
Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/2/2 <hamann.w@t-online.de>:

Pavel Stehule wrote:

but maybe https://github.com/dimitri/prefix can help

Hi Pavel,

thanks - this works perfect. However, it does not seem to play well
with the optimizer, so I ended up with

select all candidates into a temp table using prefix operator
apply all other conditions by joining that temp table to original ones

you can send proposals to enhancing to Dimitry - He will be happy :)

Regards

Pavel

Regards
Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/2/2 Pavel Stehule <pavel.stehule@gmail.com>:

2013/2/2 <hamann.w@t-online.de>:

Pavel Stehule wrote:

but maybe https://github.com/dimitri/prefix can help

Hi Pavel,

thanks - this works perfect. However, it does not seem to play well
with the optimizer, so I ended up with

select all candidates into a temp table using prefix operator
apply all other conditions by joining that temp table to original ones

seriously - it is typical solution - and it is great so PostgreSQL
help to you :)

Regards

Pavel

you can send proposals to enhancing to Dimitry - He will be happy :)

Regards

Pavel

Regards
Wolfgang

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2013-01-31, hamann.w@t-online.de <hamann.w@t-online.de> wrote:

Pavel Stehlule wrote:

Hi,

I am trying to match items from 2 tables based on a common string.
One is a big table which has one column with entries like XY123, ABC44, =

etc

The table has an index on that column.
The second table is, typically, much smaller

select .... from tab1, tab2 where tab1.code =3D tab2.code;

This works fine and fast.
Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D=

in the

big table and want them to match XY423, GF55 in the second table

Variants I have tried

select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z=

])');

Have you tried the substring function?

select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =3D
tab2.code

Hi Pavel, it was just by chance that a fixed size substring would match the
data at hand. It is more common to have a digit/letter (or vice versa) boundary
or a hyphen there

both take an enormous time. In the better case that I can subset (e.g. a=

ll candidates in table 2

share initial "AX") I get back to manageable times by adding
and tab1.code ~ '^AX'
into the recipe. Actual runtime with about a million entries in tab1 and=

800 entries in tab2

is about 40 seconds.

any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.

I see the problem since obviously every the ~ operator with a non-constant
pattern is constantly recompiling the pattern.

I wonder whether it would be possible to invent a prefix-match operator that approaches
the performance of string equality. I noted in the past (not sure whether anything
has changed in regex matching) that a constant leading part of regex would improve
performance, i.e. use an index scan to select possible candidates.

you could write a set returning function that opens cursors on both tables using
"ORDER BY code" and merges the results

--
⚂⚃ 100% natural

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general