Hi
guys,
really much
appreciated your replies.
>> You might want to include the query
plans for each server
W e use a function, the explain analyze is
quite similar:
POSTGRESQL
8.4.22:
explain
analyze select 'record.com' where 'record.com' like '%.%' and
function_cloud_view_orari('53', '192.168.10.234', 'record.com') != '' limit
1;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.03..0.04 rows=1 width=0) (actual time=1.488..1.488 rows=0 loops=1) -> Result (cost=0.03..0.04 rows=1 width=0) (actual time=1.485..1.485 rows=0 loops=1) One-Time Filter: ((function_cloud_view_orari('53'::character varying, '192.168.10.234'::character varying, 'record.com'::character varying))::text <> ''::text) Total runtime: 1.531 ms POSTGRES
9.6.1:
explain
analyze select 'record.com' where 'record.com' like '%.%' and
function_cloud_view_orari('53', '192.168.10.234', 'record.com') != '' limit
1;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.03..0.04 rows=1 width=32) (actual time=4.216..4.216 rows=0 loops=1) -> Result (cost=0.03..0.04 rows=1 width=32) (actual time=4.215..4.215 rows=0 loops=1) One-Time Filter: ((function_cloud_view_orari('53'::character varying, '192.168.10.234'::character varying, 'record.com'::character varying))::text <> ''::text) Planning time: 0.046 ms Execution time: 4.230 ms There is only
one condition that, by deleting, Query in new 9.6.1 Postgresql Server is very
fast also on massive benchmark test.
The condition
is this:
"exists ( select 1 from
gruorari where gruorari.idgrucate=grucategorie.id and (
(('{'||gg_sett||'}')::int[] && array[EXTRACT(DOW FROM NOW())::int])='t'
and now()::time between gruorari.dalle::time and gruorari.alle::time)
)"
We have a table of "weekly events", as
example:
- monday from 12 to
14
- tuesday from 18 to
20
...
What is the
best way, on Postgres, to store the information and to INDEX this kind of
information?
Thank you, very best regards! /F |