Re: R: [GENERAL] Matching indexe for timestamp
От | Adrian Klaver |
---|---|
Тема | Re: R: [GENERAL] Matching indexe for timestamp |
Дата | |
Msg-id | 35c89cdd-8ec2-1fb2-a11f-a9c458bf41b5@aklaver.com обсуждение исходный текст |
Ответ на | [GENERAL] Matching indexe for timestamp (Job <Job@colliniconsulting.it>) |
Список | pgsql-general |
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.062rows=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.countryIS NOT NULL)) > -> Nested Loop Left Join (cost=49.92..302908.01 rows=7277 width=25) (actual time=436.347..436.347 rows=1loops=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.407rows=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 withouttime 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.696rows=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=19loops=1) > Hash Cond: (grucategorie.id = gruorari_tmp.idgrucate) > -> Bitmap Heap Scan on grucategorie (cost=40.40..445.70 rows=14 width=17) (actualtime=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=14width=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=290loops=1) > Buckets: 1024 Batches: 1 Memory Usage: 29kB > -> Seq Scan on gruorari_tmp (cost=0.00..5.90 rows=290 width=36) (actualtime=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.912rows=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.095rows=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
В списке pgsql-general по дате отправления: