Re: Precomputed constants?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux