Re: Incorrect row estimates in plan?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgdba <postgresql(at)inbox(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Incorrect row estimates in plan?
Date: 2007-09-26 16:38:09
Message-ID: 22791.1190824689@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

pgdba <postgresql(at)inbox(dot)com> writes:
> Tom Lane-2 wrote:
> -> Bitmap Index Scan on slog_gri_idx
> (cost=0.00..82.26
> rows=2870 width=0) (actual time=41.306..41.306 rows=83538 loops=1)
> Index Cond: ((gid = 10000) AND (rule = ANY
> ('{1,2,8,9,10}'::integer[])) AND (CASE WHEN (rule = ANY
> ('{8,9}'::integer[])) THEN destip ELSE srcip END =
> '192.168.10.23'::inet))
>>
>> [ blink... ] Pray tell, what is the definition of this index?

> Original index: "create index slog_gri_idx on slog (gid,rule,(case when rule
> in (8,9) then
> destip else srcip end)) WHERE (rule in (1, 2, 8, 9, 10))"

> The purpose of that index is to match a specific query (one that gets run
> frequently and needs to be fast).

Ah. I didn't think you would've put such a specific thing into an index
definition, but if you're stuck supporting such badly written queries,
maybe there's no other way.

I rather doubt that you're going to be able to make this query any
faster than it is, short of buying enough RAM to keep the whole table
RAM-resident. Pulling 80000 random rows in 1200 msec doesn't sound
all that slow to me.

The ultimate solution might be to rethink your table designs ...

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message pgdba 2007-09-26 16:56:33 Re: Incorrect row estimates in plan?
Previous Message pgdba 2007-09-26 15:24:01 Re: Incorrect row estimates in plan?