Re: Query not using index pgsql 8.2.3

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 majordomo@postgresql.org so that
> your
>        message can get through to the mailing list cleanly