From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Michal Politowski <mpol+pg(at)meep(dot)pl> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: partial unique index and the planner |
Date: | 2009-02-15 18:28:45 |
Message-ID: | dcc563d10902151028l3c617c2frf51da05ed1e5ebdc@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2009/2/15 Michal Politowski <mpol+pg(at)meep(dot)pl>:
> I'm using PostgreSQL 8.3.
> Is it normal that plans using a scan on a partial unique index
> estimate that much more than one row is returned?
>
> Eg. I see:
> -> Bitmap Index Scan on tmp_idx_oss_archive_object_id_current (cost=0.00..3.12 rows=4189 width=0)
> where the tmp_idx_oss_archive_object_id_current index is a partial unique index.
>
> The estimated row count would be correct for the whole table but obviously not for
> the part covered by the unique index.
>
> This happens to be a problem in this case because then the planner
> prefers a sequence scan on a table joined to this one and a hash join to an index scan
> and a nested loop join. Which takes hundreds of milliseconds instead of
> one, so setting enable_hashjoin to false increases performance immensely.
Have you run analyze since creating the unique partial index?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-02-15 18:41:05 | Re: partial unique index and the planner |
Previous Message | Michal Politowski | 2009-02-15 17:50:06 | partial unique index and the planner |