23 mar 2007 kl. 12:33 skrev Michael Fuhr:
On Fri, Mar 23, 2007 at 11:43:54AM +0100, Henrik Zagerholm wrote:
I'm having a hard time understanding why my query is not using my
indices when I change a field in the WHERE clause.
The row count estimate for fk_filetype_id = 83 is high by an order
of magnitude:
Bitmap Index Scan on tbl_file_idx6 (cost=0.00..25.65 rows=1251
width=0) (actual time=21.958..21.958 rows=112 loops=1)
Index Cond: (fk_filetype_id = 83)
Have you run ANALYZE or VACUUM ANALYZE on these tables recently?
If so then you might try increasing the statistics target for
tbl_file.fk_filetype_id and perhaps some of the columns in the join
conditions.
I did a vacuum full and reindex on all tables.
Now I also did a vacuum analyze on tbl_acl (the biggest table with
about 4.5 millin rows)
Same result.
But do you mean if the row_count estimate is big it can't use any
index on any other table within the JOINs?
Any specific parameters I should adjust?
The query only returns 3200 rows.
Cheers,
henrik
--
Michael Fuhr
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that
your
message can get through to the mailing list cleanly
---------------------------(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
