Re: R: [GENERAL] Matching indexe for timestamp

On 01/09/2017 03:38 PM, Job wrote:

Please also reply to list.  I do not have time at the moment to go 
through this, someone else on the list might.

> Hi Adrian,
>
> You are right; here is the query and the planner.
> I think indexes are not used at all!
>
> /F
>
> EXPLAIN ANALYZE select
>       webrecord.dominio
> from webrecord
>       left join grucategorie on grucategorie.codcategoria=webrecord.categoria and grucategorie.codgruppo='f50147_01'
>       left join grulist on grulist.nome=webrecord.dominio and grulist.codgruppo='f50147_01' and grulist.stato in (1)
>       left join firewall_geo_reject on firewall_geo_reject.country=webrecord.country and firewall_geo_reject.codgruppo='f50147_01'
>       left join gruorari_tmp on gruorari_tmp.idgrucate=grucategorie.id
> where dominio='PATTERN'
>       and ( grulist.stato=1 OR grucategorie.codcategoria is not null OR firewall_geo_reject.country is not null )
>       and ( gruorari_tmp.id is null or ( 1 = gg_sett and '17:23:00'::time between gruorari_tmp.dalle and gruorari_tmp.alle ) )
>       and NOT EXISTS (select 1 from grulist where stato=2 and codgruppo='f50147_01' and nome='PATTERN')
> limit 1;
>                                                                                                          QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=130.51..172.16 rows=1 width=14) (actual time=436.537..436.538 rows=1 loops=1)
>    InitPlan 1 (returns $0)
>      ->  Index Only Scan using aaa_idx on grulist grulist_1  (cost=0.29..80.31 rows=1 width=0) (actual time=0.062..0.062 rows=0 loops=1)
>            Index Cond: ((stato = '2'::numeric) AND (codgruppo = 'f50147_01'::text) AND (nome = 'PATTERN'::text))
>            Heap Fetches: 0
>    ->  Result  (cost=50.21..303115.67 rows=7277 width=14) (actual time=436.534..436.534 rows=1 loops=1)
>          One-Time Filter: (NOT $0)
>          ->  Nested Loop Left Join  (cost=50.21..303115.67 rows=7277 width=14) (actual time=436.463..436.463 rows=1 loops=1)
>                Join Filter: ((grulist.nome)::text = (webrecord.dominio)::text)
>                Filter: ((grulist.stato = '1'::numeric) OR (grucategorie.codcategoria IS NOT NULL) OR (firewall_geo_reject.country IS NOT NULL))
>                ->  Nested Loop Left Join  (cost=49.92..302908.01 rows=7277 width=25) (actual time=436.347..436.347 rows=1 loops=1)
>                      Join Filter: ((firewall_geo_reject.country)::text = (webrecord.country)::text)
>                      Rows Removed by Join Filter: 13
>                      ->  Nested Loop Left Join  (cost=49.92..300318.08 rows=7277 width=46) (actual time=431.407..431.407 rows=1 loops=1)
>                            Join Filter: ((grucategorie.codcategoria)::text = (webrecord.categoria)::text)
>                            Rows Removed by Join Filter: 18
>                            Filter: ((gruorari_tmp.id IS NULL) OR ((1 = gruorari_tmp.gg_sett) AND ('17:23:00'::time without time zone >= gruorari_tmp.dalle) AND ('17:23:00'::time without time zone <= gruorari_tmp.alle)))
>                            ->  Seq Scan on webrecord  (cost=0.00..249584.12 rows=159614 width=70) (actual time=430.696..430.696 rows=1 loops=1)
>                                  Filter: ((dominio)::text = 'PATTERN'::text)
>                                  Rows Removed by Filter: 596858
>                            ->  Materialize  (cost=49.92..455.58 rows=14 width=35) (actual time=0.663..0.689 rows=19 loops=1)
>                                  ->  Hash Left Join  (cost=49.92..455.51 rows=14 width=35) (actual time=0.639..0.656 rows=19 loops=1)
>                                        Hash Cond: (grucategorie.id = gruorari_tmp.idgrucate)
>                                        ->  Bitmap Heap Scan on grucategorie  (cost=40.40..445.70 rows=14 width=17) (actual time=0.142..0.143 rows=19 loops=1)
>                                              Recheck Cond: ((codgruppo)::text = 'f50147_01'::text)
>                                              Heap Blocks: exact=5
>                                              ->  Bitmap Index Scan on grucategorie_codgruppo_idx  (cost=0.00..40.39 rows=14 width=0) (actual time=0.084..0.084 rows=83 loops=1)
>                                                    Index Cond: ((codgruppo)::text = 'f50147_01'::text)
>                                        ->  Hash  (cost=5.90..5.90 rows=290 width=36) (actual time=0.381..0.381 rows=290 loops=1)
>                                              Buckets: 1024  Batches: 1  Memory Usage: 29kB
>                                              ->  Seq Scan on gruorari_tmp  (cost=0.00..5.90 rows=290 width=36) (actual time=0.023..0.176 rows=290 loops=1)
>                      ->  Materialize  (cost=0.00..297.73 rows=21 width=3) (actual time=0.151..4.928 rows=13 loops=1)
>                            ->  Seq Scan on firewall_geo_reject  (cost=0.00..297.62 rows=21 width=3) (actual time=0.141..4.912 rows=13 loops=1)
>                                  Filter: ((codgruppo)::text = 'f50147_01'::text)
>                                  Rows Removed by Filter: 15717
>                ->  Materialize  (cost=0.29..80.31 rows=1 width=19) (actual time=0.109..0.109 rows=0 loops=1)
>                      ->  Index Scan using bbb_idx on grulist  (cost=0.29..80.31 rows=1 width=19) (actual time=0.095..0.095 rows=0 loops=1)
>                            Index Cond: (((codgruppo)::text = 'f50147_01'::text) AND (stato = '1'::numeric))
>                            Filter: ((nome)::text = 'PATTERN'::text)
>                            Rows Removed by Filter: 1
>  Planning time: 14.996 ms
>  Execution time: 436.840 ms
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com