COU indexes

CREATE INDEX status_topographicline_idx ON osmm.topographicline(status);
COMMIT;
CREATE INDEX status_topographicpoint_idx ON osmm.topographicpoint(status);
COMMIT;

CREATE INDEX status_boundaryline_idx ON osmm.boundaryline(status);
COMMIT;
CREATE INDEX status_topographicarea_idx ON osmm.topographicarea(status);
COMMIT;

CREATE INDEX status_cartographicsymbol_idx ON osmm.cartographicsymbol(status);
COMMIT;

CREATE INDEX status_cartographictext_idx ON osmm.cartographictext(status);
COMMIT;





CREATE INDEX version_date_topographicline_idx ON osmm.topographicline (version_date);
COMMIT;

CREATE INDEX change_date_topographciline_idx ON osmm.topographicline (change_date);
COMMIT;






CREATE INDEX daterange_topographicline_idx ON osmm.topographicline USING GiST
 (tsrange(version_date, change_date, '[]'));


SELECT *
FROM   osmm.topographicline
WHERE  --status = 'D'
--AND    
tsrange(version_date, change_date, '[]')
    @> tsrange('2005-01-20', '2005-07-09', '[]')



    select * from osmm.topographicline as t 
where t.status = 'U' 
and t.version_date >= date'2005-02-06'
and t.change_date <= date'2005-10-01'






    select * from osmm.topographicline as t 
where t.status = 'U' 
and t.version_date >= date'2005-02-06'
and t.change_date <= date'2005-10-01' 
--Original query with no indexes 52729ms

CREATE INDEX status_topographicline_idx ON osmm.topographicline(status);
COMMIT;--Query returned successfully with no result in 256841 ms.

--After adding status index = 7302ms

CREATE INDEX version_date_topographicline_idx ON osmm.topographicline (version_date);
COMMIT;--Query returned successfully with no result in 71534 ms.

CREATE INDEX change_date_topographciline_idx ON osmm.topographicline (change_date);
COMMIT;--Query returned successfully with no result in 63409 ms.

--After adding indexes --5594ms