Re: [GENERAL] Problem with planner choosing nested loop

2008-04-03 Thread Craig Ringer
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...

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Alban Hertroys
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

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Scott Marlowe
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. > >

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Harald Fuchs
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

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Rodrigo E. De León Plicet
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

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Alex Solovey
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

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Scott Marlowe
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

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Alex Solovey
> - 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

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Rodrigo E. De León Plicet
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

Re: [GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Rodrigo E. De León Plicet
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

[GENERAL] Problem with planner choosing nested loop

2008-04-02 Thread Alex Solovey
Hello, I was trying to optimize a slow query in database running 8.3.1. It turned out that planner is choosing nested loop join resulting in multiple sequential scans over the long table. Here is a simplified database schema, consisting of two tables: CREATE TABLE bar ( bar_id in