2011/8/9 hubert depesz lubaczewski :
> On Tue, Aug 09, 2011 at 04:08:39PM -0400, Tom Lane wrote:
>> I suppose what's going on here is that the "state" and "ending_tsz"
>> columns are highly correlated, such that there are lots of 'active'
>> items but hardly any of them ended more than a day ago?
On Tue, Aug 09, 2011 at 05:18:38PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski writes:
> > I solved the problem by adding "enable_bitmapscan = false" (and keeping
> > the query in original format, with subselect) which caused the plan to
> > be ok.
>
> I doubt that solution is any more rob
hubert depesz lubaczewski writes:
> I solved the problem by adding "enable_bitmapscan = false" (and keeping
> the query in original format, with subselect) which caused the plan to
> be ok.
I doubt that solution is any more robust than what you had before ---
in particular, it's likely to fall ba
On Tue, Aug 09, 2011 at 04:08:39PM -0400, Tom Lane wrote:
> I suppose what's going on here is that the "state" and "ending_tsz"
> columns are highly correlated, such that there are lots of 'active'
> items but hardly any of them ended more than a day ago? If so,
yes, that's correct.
> you're goi
hubert depesz lubaczewski writes:
> version with disabled bitmapscans:
> $ explain analyze select count(*) from objects where state='active' and
> ending_tsz <= now() - '1 day'::interval;
> QUERY PLAN
> -
On Tue, Aug 09, 2011 at 12:11:48PM -0400, Tom Lane wrote:
> There's absolutely no hope of getting the planner to make intelligent
> plan choices when its estimates are that far off (3344138 rows estimated
> versus none actual). It's just luck if you get the "right" plan despite
> that.
> Now part
On Tue, Aug 09, 2011 at 12:11:48PM -0400, Tom Lane wrote:
> Now part of the reason why the estimate is so bad is the sub-select,
> which completely defeats selectivity estimation for the ending_tsz
> condition. Could we see EXPLAIN ANALYZE for the query without a
> sub-select, both with and withou
hubert depesz lubaczewski writes:
> as for good plan. sure. If i'll disable bitmap scans, I get:
> $ explain analyze select count(*) from objects where state='active' and
> ending_tsz <= (select now() - '1 day'::interval );
>
On Tue, Aug 09, 2011 at 05:11:09PM +0200, Cédric Villemain wrote:
> The plan turn bad without any new ANALYZE, right ?
Right.
> does the table increase more quickly now than before ? is it now way
> larger than before ?
> Also, do you have an explain with the 'good' plan ?
changes in the object
2011/8/8 hubert depesz lubaczewski :
> Hi,
> we have 8.3.11 installation on client site, with table, which looks like
> this:
>
> $ \d objects
> Table "public.objects"
> Column | Type |
> Mo
On Mon, Aug 08, 2011 at 10:01:58PM +0200, pasman pasmański wrote:
> Try to change index: objects_endings_tsz_active(state,endings_tsz)
> where state='active'.
adding new indexes on this machine is quite complicated, as it's heavily
used production system.
And the current index should be chosen by
Try to change index: objects_endings_tsz_active(state,endings_tsz)
where state='active'.
2011/8/8, hubert depesz lubaczewski :
> Hi,
> we have 8.3.11 installation on client site, with table, which looks like
> this:
>
> $ \d objects
> Table "public.obj
On Mon, Aug 08, 2011 at 12:51:40PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski writes:
> > and we have a query:
> > select count(*) from objects where state='active'::text and ending_tsz <= (
> > select now() - '1 day'::interval );
>
> Try getting rid of the sub-select. There might have
hubert depesz lubaczewski writes:
> and we have a query:
> select count(*) from objects where state='active'::text and ending_tsz <= (
> select now() - '1 day'::interval );
Try getting rid of the sub-select. There might have been a reason to do
it like that ten years ago, but these days it's a
Alban Hertroys wrote:
>
> On Apr 2, 2008, at 9:02 PM, Alex Solovey wrote:
>> The reduced database example has the same problem in EXPLAIN ANALYZE
>> as production one, here:
>>
>> Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
>> time=0.098..3.561 rows=24 loops=1)
>
> Hang on...
On Apr 2, 2008, at 9:02 PM, Alex Solovey wrote:
The reduced database example has the same problem in EXPLAIN
ANALYZE as production one, here:
Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
time=0.098..3.561 rows=24 loops=1)
Hang on... You prefer sequential scans because i
On Wed, Apr 2, 2008 at 1:06 PM, Harald Fuchs <[EMAIL PROTECTED]> wrote:
> In article <[EMAIL PROTECTED]>,
>
> "Rodrigo E. De León Plicet" <[EMAIL PROTECTED]> writes:
>
> > On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
> >> ... I have no idea how it could be fixed.
>
>
In article <[EMAIL PROTECTED]>,
"Rodrigo E. De León Plicet" <[EMAIL PROTECTED]> writes:
> On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
>> ... I have no idea how it could be fixed.
> - CREATE INDEX xifoo ON foo(bar_id);
> - ANALYZE;
> - Retry.
A compound index
CREATE
On Wed, Apr 2, 2008 at 1:20 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
> In this simple (which means "reduced") test database, yes. But the actual
> table "foo" in production database:
>
> 1. partitioned, with 50+ partitions
> 2. heavily updated (and indexes make it slow)
> 3. has more fields
The reduced database example has the same problem in EXPLAIN ANALYZE as
production one, here:
Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
time=0.098..3.561 rows=24 loops=1)
That's why I posted the smallest dataset I could reproduce the problem with.
Rodrigo E. De León Pli
On Wed, Apr 2, 2008 at 12:12 PM, Rodrigo E. De León Plicet
<[EMAIL PROTECTED]> wrote:
> Also important, consider creating additional indexes based on your
> access patterns.
Good point. Note that you can create indexes and then track their
usefulness with the pg_stat_user_indexes view, which I f
> - CREATE INDEX xifoo ON foo(bar_id);
In this simple (which means "reduced") test database, yes. But the
actual table "foo" in production database:
1. partitioned, with 50+ partitions
2. heavily updated (and indexes make it slow)
3. has more fields like bar_id
We had indexes on several fiel
Also important, consider creating additional indexes based on your
access patterns.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
> ... I have no idea how it could be fixed.
- CREATE INDEX xifoo ON foo(bar_id);
- ANALYZE;
- Retry.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.po
24 matches
Mail list logo
