pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Disabling nested loops - worst case performance
8+ messages / 5 participants
[nested] [flat]
* Disabling nested loops - worst case performance
@ 2011-03-18 07:15 Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 07:02 ` Re: Disabling nested loops - worst case performance Pavel Stehule <pavel.stehule@gmail.com>
2011-03-18 10:52 ` Re: Disabling nested loops - worst case performance Vitalii Tymchyshyn <tivv00@gmail.com>
2011-03-18 11:14 ` Re: Disabling nested loops - worst case performance Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 3 replies; 8+ messages in thread
From: Anssi Kääriäinen @ 2011-03-18 07:15 UTC (permalink / raw)
To: pgsql-performance
Hello list,
I am working on a Entity-Attribute-Value (EAV) database using PostgreSQL
8.4.7. The basic problem is that when joining multiple times different
entities the planner thinks that there is vastly less rows to join than
there is in reality and decides to use multiple nested loops for the
join chain. This results in queries where when nested loops are enabled,
query time is somewhere around 35 seconds, but with nested loops
disabled, the performance is somewhere around 100ms. I don't think there
is much hope for getting better statistics, as EAV is just not
statistics friendly. The values of an attribute depend on the type of
the attribute, and different entities have different attributes defined.
The planner has no idea of these correlations.
Now, my question is: if I disable nested loops completely for the users
of the EAV database what kind of worst case performance loss can I
expect? I don't mind if a query that normally runs in 100ms now takes
200ms, but about problems where the query will take much more time to
complete than with nested loops enabled. As far as I understand these
cases should be pretty rare if non-existent?
- Anssi
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disabling nested loops - worst case performance
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
@ 2011-03-18 07:02 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-03-18 10:58 ` Re: Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2011-03-18 07:02 UTC (permalink / raw)
To: Anssi Kääriäinen <anssi.kaariainen@thl.fi>; +Cc: pgsql-performance
Hello
for example queries with LIMIT clause can be significantly faster with
nested loop. But you don't need to disable nested loop globally.
You can wrap your query to sql functions and disable nested loop just
for these functions.
Regards
Pavel Stehule
2011/3/18 Anssi Kääriäinen <anssi.kaariainen@thl.fi>:
> Hello list,
>
> I am working on a Entity-Attribute-Value (EAV) database using PostgreSQL
> 8.4.7. The basic problem is that when joining multiple times different
> entities the planner thinks that there is vastly less rows to join than
> there is in reality and decides to use multiple nested loops for the join
> chain. This results in queries where when nested loops are enabled, query
> time is somewhere around 35 seconds, but with nested loops disabled, the
> performance is somewhere around 100ms. I don't think there is much hope for
> getting better statistics, as EAV is just not statistics friendly. The
> values of an attribute depend on the type of the attribute, and different
> entities have different attributes defined. The planner has no idea of these
> correlations.
>
> Now, my question is: if I disable nested loops completely for the users of
> the EAV database what kind of worst case performance loss can I expect? I
> don't mind if a query that normally runs in 100ms now takes 200ms, but about
> problems where the query will take much more time to complete than with
> nested loops enabled. As far as I understand these cases should be pretty
> rare if non-existent?
>
> - Anssi
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disabling nested loops - worst case performance
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 07:02 ` Re: Disabling nested loops - worst case performance Pavel Stehule <pavel.stehule@gmail.com>
@ 2011-03-18 10:58 ` Anssi Kääriäinen <anssi.kaariainen@thl.fi>
0 siblings, 0 replies; 8+ messages in thread
From: Anssi Kääriäinen @ 2011-03-18 10:58 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
On 03/18/2011 09:02 AM, Pavel Stehule wrote:
> for example queries with LIMIT clause can be significantly faster with
> nested loop. But you don't need to disable nested loop globally.
>
> You can wrap your query to sql functions and disable nested loop just
> for these functions.
Thank you for your help, the LIMIT example was something I was not aware of.
The problem is we are replacing an old database, and we need to
replicate certain views for external users. Minimal impact for these
users is required. Maybe it would be best to create special user
accounts for these external users and disable nested loops only for
those accounts. Otherwise we will disable nested loops when absolutely
necessary.
- Anssi
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disabling nested loops - worst case performance
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
@ 2011-03-18 10:52 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2011-03-18 11:59 ` Re: Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 15:26 ` Re: Disabling nested loops - worst case performance Claudio Freire <klaussfreire@gmail.com>
2 siblings, 2 replies; 8+ messages in thread
From: Vitalii Tymchyshyn @ 2011-03-18 10:52 UTC (permalink / raw)
To: Anssi Kääriäinen <anssi.kaariainen@thl.fi>; +Cc: pgsql-performance
18.03.11 09:15, Anssi Kääriäinen написав(ла):
> Hello list,
>
> I am working on a Entity-Attribute-Value (EAV) database using
> PostgreSQL 8.4.7. The basic problem is that when joining multiple
> times different entities the planner thinks that there is vastly less
> rows to join than there is in reality and decides to use multiple
> nested loops for the join chain. This results in queries where when
> nested loops are enabled, query time is somewhere around 35 seconds,
> but with nested loops disabled, the performance is somewhere around
> 100ms. I don't think there is much hope for getting better statistics,
> as EAV is just not statistics friendly. The values of an attribute
> depend on the type of the attribute, and different entities have
> different attributes defined. The planner has no idea of these
> correlations.
Hello.
If your queries work on single attribute, you can try adding partial
indexes for different attributes. Note that in this case parameterized
statements may prevent index usage, so check also with attribute id inlined.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disabling nested loops - worst case performance
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 10:52 ` Re: Disabling nested loops - worst case performance Vitalii Tymchyshyn <tivv00@gmail.com>
@ 2011-03-18 11:59 ` Anssi Kääriäinen <anssi.kaariainen@thl.fi>
1 sibling, 0 replies; 8+ messages in thread
From: Anssi Kääriäinen @ 2011-03-18 11:59 UTC (permalink / raw)
To: Vitalii Tymchyshyn <tivv00@gmail.com>; +Cc: pgsql-performance
On 03/18/2011 12:52 PM, Vitalii Tymchyshyn wrote:
> If your queries work on single attribute, you can try adding partial
> indexes for different attributes. Note that in this case parameterized
> statements may prevent index usage, so check also with attribute id inlined.
>
> Best regards, Vitalii Tymchyshyn
Unfortunately this does not help for the statistics, and (I guess)
nested loops will still be used when joining:
hot2=> explain analyze select * from attr_value where attr_tunniste =
'suhde_hyvaksytty' and arvo_text = 't';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using attr_value_arvo_text_idx1 on attr_value
(cost=0.00..343.59 rows=152 width=118) (actual time=0.076..7.768
rows=3096 loops=1)
Index Cond: (arvo_text = 't'::text)
Filter: ((attr_tunniste)::text = 'suhde_hyvaksytty'::text)
Total runtime: 10.855 ms
(4 rows)
hot2=> create index suhde_hyvaksytty_idx on attr_value(arvo_text) where
attr_tunniste = 'suhde_hyvaksytty';
CREATE INDEX
hot2=> analyze attr_value;
hot2=> explain analyze select * from attr_value where attr_tunniste =
'suhde_hyvaksytty' and arvo_text = 't';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using suhde_hyvaksytty_idx on attr_value (cost=0.00..43.72
rows=152 width=118) (actual time=0.093..4.776 rows=3096 loops=1)
Index Cond: (arvo_text = 't'::text)
Total runtime: 7.817 ms
(3 rows)
- Anssi
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disabling nested loops - worst case performance
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 10:52 ` Re: Disabling nested loops - worst case performance Vitalii Tymchyshyn <tivv00@gmail.com>
@ 2011-03-18 15:26 ` Claudio Freire <klaussfreire@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Claudio Freire @ 2011-03-18 15:26 UTC (permalink / raw)
To: pgsql-performance
On Fri, Mar 18, 2011 at 7:52 AM, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
> 18.03.11 09:15, Anssi Kääriäinen написав(ла):
> Hello.
>
> If your queries work on single attribute, you can try adding partial indexes
> for different attributes. Note that in this case parameterized statements
> may prevent index usage, so check also with attribute id inlined.
And if your queries work on a single entity instead, you can partition
the table per-entity thus "teach" the database enging about the
correlation.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disabling nested loops - worst case performance
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
@ 2011-03-18 11:14 ` Thomas Kellerer <spam_eater@gmx.net>
2011-03-18 12:19 ` Re: Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2 siblings, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2011-03-18 11:14 UTC (permalink / raw)
To: pgsql-performance
Anssi Kääriäinen, 18.03.2011 08:15:
> Hello list,
>
> I am working on a Entity-Attribute-Value (EAV) database using
> PostgreSQL 8.4.7. The basic problem is that when joining multiple
> times different entities the planner thinks that there is vastly less
> rows to join than there is in reality and decides to use multiple
> nested loops for the join chain.
Did you consider using hstore instead?
I think in the PostgreSQL world, this is a better alternative than EAV and most probably faster as well.
Regards
Thomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disabling nested loops - worst case performance
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 11:14 ` Re: Disabling nested loops - worst case performance Thomas Kellerer <spam_eater@gmx.net>
@ 2011-03-18 12:19 ` Anssi Kääriäinen <anssi.kaariainen@thl.fi>
0 siblings, 0 replies; 8+ messages in thread
From: Anssi Kääriäinen @ 2011-03-18 12:19 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-performance
On 03/18/2011 01:14 PM, Thomas Kellerer wrote:
> Did you consider using hstore instead?
>
> I think in the PostgreSQL world, this is a better alternative than EAV and most probably faster as well.
No, we did not. The reason is that we want to track each attribute with
bi-temporal timestamps. The actual database schema for the attribute
value table is:
CREATE TABLE attr_value (
id SERIAL PRIMARY KEY,
olio_id INTEGER NOT NULL REFERENCES base_olio, -- entity identifier
attr_tunniste VARCHAR(20) NOT NULL REFERENCES base_attr, -- attr
identifier
kieli_tunniste VARCHAR(20) REFERENCES kieli, -- lang identifier
arvo_number DECIMAL(18, 9), -- value number
arvo_ts timestamptz, -- value timestamp
arvo_text TEXT, -- value text
arvo_valinta_tunniste VARCHAR(20), -- for choice lists:
"value_choice_identifier"
real_valid_from TIMESTAMPTZ NOT NULL, -- real_valid_from -
real_valid_until define when things have been in "real" world
real_valid_until TIMESTAMPTZ NOT NULL,
db_valid_from TIMESTAMPTZ NOT NULL, -- db_valid_* defines when
things have been in the database
db_valid_until TIMESTAMPTZ NOT NULL,
tx_id_insert INTEGER default txid_current(),
tx_id_delete INTEGER,
-- foreign keys & checks skipped
);
Naturally, we have other tables defining the objects, joins between
objects and metadata for the EAV. All data modifications are done
through procedures, which ensure uniqueness etc. for the attributes and
joins.
The data set is small, and performance in general is not that important,
as long as the UI is responsive and data can be transferred to other
systems in reasonable time. Insert performance is at least 10x worse
than when using traditional schema, but it doesn't matter (we have
somewhere around 1000 inserts / updates a day max). The only real
problem so far is the chained nested loop problem, which really kills
performance for some queries.
Surprisingly (at least to me) this schema has worked really well,
although sometimes there is a feeling that we are implementing a
database using a database...
- Anssi
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-03-18 15:26 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-18 07:15 Disabling nested loops - worst case performance Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 07:02 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-03-18 10:58 ` Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 10:52 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2011-03-18 11:59 ` Anssi Kääriäinen <anssi.kaariainen@thl.fi>
2011-03-18 15:26 ` Claudio Freire <klaussfreire@gmail.com>
2011-03-18 11:14 ` Thomas Kellerer <spam_eater@gmx.net>
2011-03-18 12:19 ` Anssi Kääriäinen <anssi.kaariainen@thl.fi>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox