pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPrecomputed constants?
8+ messages / 5 participants
[nested] [flat]
* Precomputed constants?
@ 2006-06-14 10:53 Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Re: Precomputed constants? Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 1 reply; 8+ messages in thread
From: Böszörményi Zoltán @ 2006-06-14 10:53 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Precomputed constants?
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
@ 2006-06-14 11:07 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-06-14 11:30 ` Re: Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
0 siblings, 1 reply; 8+ messages in thread
From: Volkan YAZICI @ 2006-06-14 11:07 UTC (permalink / raw)
To: Böszörményi Zoltán <zboszor@dunaweb.hu>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Precomputed constants?
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Re: Precomputed constants? Volkan YAZICI <yazicivo@ttnet.net.tr>
@ 2006-06-14 11:30 ` Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 15:18 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 8+ messages in thread
From: Böszörményi Zoltán @ 2006-06-14 11:30 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttnet.net.tr>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Precomputed constants?
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Re: Precomputed constants? Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-06-14 11:30 ` Re: Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
@ 2006-06-14 15:18 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 04:31 ` Re: Precomputed constants? Zoltan Boszormenyi <zboszor@dunaweb.hu>
0 siblings, 1 reply; 8+ messages in thread
From: Jim C. Nasby @ 2006-06-14 15:18 UTC (permalink / raw)
To: B?sz?rm?nyi Zolt?n <zboszor@dunaweb.hu>; +Cc: Volkan YAZICI <yazicivo@ttnet.net.tr>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Precomputed constants?
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Re: Precomputed constants? Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-06-14 11:30 ` Re: Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 15:18 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-15 04:31 ` Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-15 15:59 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 8+ messages in thread
From: Zoltan Boszormenyi @ 2006-06-15 04:31 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Volkan YAZICI <yazicivo@ttnet.net.tr>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Precomputed constants?
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Re: Precomputed constants? Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-06-14 11:30 ` Re: Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 15:18 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 04:31 ` Re: Precomputed constants? Zoltan Boszormenyi <zboszor@dunaweb.hu>
@ 2006-06-15 15:59 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 18:19 ` Re: Precomputed constants? Zoltan Boszormenyi <zboszor@dunaweb.hu>
0 siblings, 1 reply; 8+ messages in thread
From: Jim C. Nasby @ 2006-06-15 15:59 UTC (permalink / raw)
To: Zoltan Boszormenyi <zboszor@dunaweb.hu>; +Cc: Volkan YAZICI <yazicivo@ttnet.net.tr>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Precomputed constants?
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Re: Precomputed constants? Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-06-14 11:30 ` Re: Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 15:18 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 04:31 ` Re: Precomputed constants? Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-15 15:59 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-15 18:19 ` Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-17 19:25 ` Re: Precomputed constants? Jim Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 8+ messages in thread
From: Zoltan Boszormenyi @ 2006-06-15 18:19 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Volkan YAZICI <yazicivo@ttnet.net.tr>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Precomputed constants?
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Re: Precomputed constants? Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-06-14 11:30 ` Re: Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 15:18 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 04:31 ` Re: Precomputed constants? Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-15 15:59 ` Re: Precomputed constants? Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 18:19 ` Re: Precomputed constants? Zoltan Boszormenyi <zboszor@dunaweb.hu>
@ 2006-06-17 19:25 ` Jim Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim Nasby @ 2006-06-17 19:25 UTC (permalink / raw)
To: Zoltan Boszormenyi <zboszor@dunaweb.hu>; +Cc: Volkan YAZICI <yazicivo@ttnet.net.tr>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-06-17 19:25 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-14 10:53 Precomputed constants? Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 11:07 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-06-14 11:30 ` Böszörményi Zoltán <zboszor@dunaweb.hu>
2006-06-14 15:18 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 04:31 ` Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-15 15:59 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-15 18:19 ` Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-17 19:25 ` Jim Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox