On 2008-02-05 Viviane Lestic wrote: > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------- > Sort (cost=2345.54..2345.58 rows=16 width=308) (actual > time=270638.774..270643.142 rows=7106 loops=1) > Sort Key: rank(tab_ocr.zoneindex_test, q.q) > -> Nested Loop (cost=80.04..2345.22 rows=16 width=308) (actual > time=40886.553..270619.730 rows=7106 loops=1) > -> Nested Loop (cost=80.04..1465.76 rows=392 width=308) (actual > time=38209.193..173932.313 rows=272414 loops=1) > -> Function Scan on q (cost=0.00..0.01 rows=1 width=32) (actual > time=0.006..0.007 rows=1 loops=1) > -> Bitmap Heap Scan on tab_ocr (cost=80.04..1460.85 rows=392 > width=276) (actual time=38209.180..173507.052 rows=272414 loops=1) > Filter: (tab_ocr.zoneindex_test @@ q.q) > -> Bitmap Index Scan on zoneindex_test_idx (cost=0.00..79.94 rows=392 > width=0) (actual time=38204.261..38204.261 rows=283606 loops=1) > Index Cond: (tab_ocr.zoneindex_test @@ q.q) > -> Index Scan using tab_chemin_label_index on tab_chemin > (cost=0.00..2.23 rows=1 width=4) (actual time=0.036..0.036 rows=0 > loops=272414) > Index Cond: (tab_ocr.idstruct = tab_chemin.label) > Filter: ((chemin)::text ~~ '%;2;%'::text) > Total runtime: 270647.946 ms > ************************************************************************** > > Could someone help me analyze this problem? Your planner estimates are way off. Try increasing the statistics target for the columns used in this query and re-analyze the tables after doing so. Regards Ansgar Wiechers -- "The Mac OS X kernel should never panic because, when it does, it seriously inconveniences the user." --http://developer.apple.com/technotes/tn2004/tn2118.html ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
