Il nostro punto della strada

User Tools

Site Tools

Trace:
sysadmin:postgresql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Last revisionBoth sides next revision
sysadmin:postgresql [2015/02/12 08:57] – CREAZIONE E USO INDICI ferodasysadmin:postgresql [2015/02/12 09:01] – Indice su espressione feroda
Line 106: Line 106:
    Filter: (duration IS NOT NULL)    Filter: (duration IS NOT NULL)
   Total runtime: 46.523 ms (3 rows)   Total runtime: 46.523 ms (3 rows)
 +  
 +===== Se creo indice sull'espressione "duration IS NOT NULL" cosa succede? =====
 +
 +Niente .... :(
 +
 +    postgres=# CREATE INDEX ON "acs_service".connection_session ((duration IS NOT NULL));
 +    CREATE INDEX
 +    postgres=# REINDEX TABLE "acs_service"."connection_session";
 +    REINDEX
 +    postgres=# EXPLAIN ANALYZE SELECT bcs.id, bcs.duration FROM acs_service.connection_session bcs WHERE bcs.duration IS NOT NULL;
 +                                                              QUERY PLAN                                                       
 +    -------------------------------------------------------------------------------------------------------------------------------
 +     Seq Scan on connection_session bcs  (cost=0.00..2829.90 rows=149220 width=20) (actual time=0.011..38.323 rows=149226 loops=1)
 +       Filter: (duration IS NOT NULL)
 +     Total runtime: 48.491 ms
 +    (3 rows)
 +
 +    postgres=# EXPLAIN ANALYZE SELECT bcs.id, bcs.duration FROM acs_service.connection_session bcs WHERE bcs.duration IS NULL;         
 +                                                                    QUERY PLAN                                                            
 +    ------------------------------------------------------------------------------------------------------------------------------------------
 +     Bitmap Heap Scan on connection_session bcs  (cost=4.82..234.49 rows=70 width=20) (actual time=0.061..0.119 rows=65 loops=1)
 +       Recheck Cond: (duration IS NULL)
 +       ->  Bitmap Index Scan on connection_session_duration_idx  (cost=0.00..4.80 rows=70 width=0) (actual time=0.050..0.050 rows=66 loops=1)
 +             Index Cond: (duration IS NULL)
 +     Total runtime: 0.148 ms
 +    (5 rows)
 +
  
 ====== Occupazione disco ====== ====== Occupazione disco ======