Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group