Thread: Precomputed constants?
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
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.
> 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
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
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
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
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
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