Thread: Precomputed constants?

Precomputed constants?

From
Böszörményi Zoltán
Date:
Hi,

here's my problem:

# explain analyze select * from mxstrpartsbg where szam =
round(800000*random())::integer;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on mxstrpartsbg  (cost=0.00..56875.04 rows=1 width=322) (actual
time=190.748..1271.664 rows=1 loops=1)
   Filter: (szam = (round((800000::double precision * random())))::integer)
 Total runtime: 1271.785 ms
(3 rows)

# explain analyze select * from mxstrpartsbg where szam = 671478;
                                                            QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using mxstrpartsbg_pkey on mxstrpartsbg  (cost=0.00..5.87
rows=1 width=322) (actual time=71.642..71.644 rows=1 loops=1)
   Index Cond: (szam = 671478)
 Total runtime: 71.706 ms
(3 rows)

Is there a way to have PostgreSQL to pre-compute all the constants in the
WHERE clause? It would be a huge performance gain. Thanks in advance.

Best regards,
Zoltán Böszörményi


Re: Precomputed constants?

From
Volkan YAZICI
Date:
On Jun 14 12:53, Böszörményi Zoltán wrote:
> # explain analyze select * from mxstrpartsbg where szam =
> round(800000*random())::integer;

AFAIK, you can use sth like that:

SELECT * FROM mxstrpartsbg
  WHERE szam = (SELECT round(800000*random())::integer OFFSET 0);

This will prevent calculation of round() for every row.


Regards.

Re: Precomputed constants?

From
Böszörményi Zoltán
Date:
> On Jun 14 12:53, Böszörményi Zoltán wrote:
>> # explain analyze select * from mxstrpartsbg where szam =
>> round(800000*random())::integer;
>
> AFAIK, you can use sth like that:
>
> SELECT * FROM mxstrpartsbg
>   WHERE szam = (SELECT round(800000*random())::integer OFFSET 0);
>
> This will prevent calculation of round() for every row.
>
> Regards.

Thanks, It worked.

Oh, I see now. I makes sense, random() isn't a constant and
it was computed for every row. Actually running the query produces
different results sets with 0, 1 or 2 rows.

Replacing random() with a true constant gives me index scan
even if it's hidden inside other function calls. E.g.:

# explain analyze select * from mxstrpartsbg where szam =
round('800000.71'::decimal(10,2))::integer;
                                                            QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using mxstrpartsbg_pkey on mxstrpartsbg  (cost=0.00..5.87
rows=1 width=322) (actual time=0.020..0.022 rows=1 loops=1)
   Index Cond: (szam = 800001)
 Total runtime: 0.082 ms
(3 rows)

Best regards,
Zoltán Böszörményi


Re: Precomputed constants?

From
"Jim C. Nasby"
Date:
On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
> Replacing random() with a true constant gives me index scan
> even if it's hidden inside other function calls. E.g.:

The database has no choice but to compute random() for every row; it's
marked VOLATILE.
--
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

Re: Precomputed constants?

From
Zoltan Boszormenyi
Date:
Jim C. Nasby írta:
> On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
>
>> Replacing random() with a true constant gives me index scan
>> even if it's hidden inside other function calls. E.g.:
>>
>
> The database has no choice but to compute random() for every row; it's
> marked VOLATILE.
>

I see now, docs about CREATE FUNCTION mentions random(),
currval() and timeofday() as examples for VOLATILE.
But where in the documentation can I find this info about all
built-in functions? Thanks.

Best regards,
Zoltán Böszörményi


Re: Precomputed constants?

From
"Jim C. Nasby"
Date:
On Thu, Jun 15, 2006 at 06:31:02AM +0200, Zoltan Boszormenyi wrote:
> Jim C. Nasby ?rta:
> >On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
> >
> >>Replacing random() with a true constant gives me index scan
> >>even if it's hidden inside other function calls. E.g.:
> >>
> >
> >The database has no choice but to compute random() for every row; it's
> >marked VOLATILE.
> >
>
> I see now, docs about CREATE FUNCTION mentions random(),
> currval() and timeofday() as examples for VOLATILE.
> But where in the documentation can I find this info about all
> built-in functions? Thanks.

No, but you can query pg_proc for that info. The docs should have info
about that table.
--
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

Re: Precomputed constants?

From
Zoltan Boszormenyi
Date:
Jim C. Nasby írta:
> On Thu, Jun 15, 2006 at 06:31:02AM +0200, Zoltan Boszormenyi wrote:
>
>> Jim C. Nasby ?rta:
>>
>>> On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
>>>
>>>
>>>> Replacing random() with a true constant gives me index scan
>>>> even if it's hidden inside other function calls. E.g.:
>>>>
>>>>
>>> The database has no choice but to compute random() for every row; it's
>>> marked VOLATILE.
>>>
>>>
>> I see now, docs about CREATE FUNCTION mentions random(),
>> currval() and timeofday() as examples for VOLATILE.
>> But where in the documentation can I find this info about all
>> built-in functions? Thanks.
>>
>
> No, but you can query pg_proc for that info. The docs should have info
> about that table.
>

Thanks!

# select proname,provolatile from pg_proc where proname='random';
 proname | provolatile
---------+-------------
 random  | v
(1 sor)

# select distinct provolatile from pg_proc;
 provolatile
-------------
 i
 s
 v
(3 sor)

If I get this right, IMMUTABLE/STABLE/VOLATILE
are indicated with their initials.

Best regards,
Zoltán Böszörményi


Re: Precomputed constants?

From
Jim Nasby
Date:
On Jun 15, 2006, at 1:19 PM, Zoltan Boszormenyi wrote:
> # select distinct provolatile from pg_proc;
> provolatile
> -------------
> i
> s
> v
> (3 sor)
>
> If I get this right, IMMUTABLE/STABLE/VOLATILE
> are indicated with their initials.

That's probably correct. If the docs don't specify this then the code
would. Or you could just create 3 test functions and see what you end
up with, but I can't see it being any different from your guess.
--
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