pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery performance with hstore vs. non-hstore
7+ messages / 3 participants
[nested] [flat]
* query performance with hstore vs. non-hstore
@ 2014-09-01 06:10 Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 7+ messages in thread
From: Huang, Suya @ 2014-09-01 06:10 UTC (permalink / raw)
To: pgsql-performance
Hi ,
I'm tweaking table layout to get better performance of query. One table doesn't use hstore but expand all metrics of cha_type to different rows. The other table has hstore for metrics column as cha_type->metrics so it has less records than the first one.
I would be expecting the query on seconds table has better performance than the first one. However, it's not the case at all. I'm wondering if there's something wrong with my execution plan? With the hstore table, the optimizer has totally wrong estimation on row counts at hash aggregate stage and it takes 34 seconds on hash-join,25 seconds on hash-aggregate, 10 seconds on sort. However, with non-hstore table, it takes 17 seconds on hash join, 18 seconds on hashaggregate and 2 seconds on sort.
Can someone help me to explain why this is happening? And is there a way to fine-tune the query?
Table structure
dev=# \d+ weekly_non_hstore
Table "test.weekly_non_hstore"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------------------------+-----------+----------+--------------+-------------
date | date | | plain | |
ref_id | character varying(256) | | extended | |
cha_typel | text | | extended | |
visits | double precision | | plain | |
pages | double precision | | plain | |
duration | double precision | | plain | |
Has OIDs: no
Tablespace: "tbs_data"
dev=# \d+ weekly_hstore
Table "test.weekly_hstore"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------------------------+-----------+----------+--------------+-------------
date | date | | plain | |
ref_id | character varying(256) | | extended | |
visits | hstore | | extended | |
pages | hstore | | extended | |
duration | hstore | | extended | |
Has OIDs: no
Tablespace: "tbs_data"
dev=# select count(*) from weekly_non_hstore;
count
----------
71818882
(1 row)
dev=# select count(*) from weekly_hstore;
count
---------
1292314
(1 row)
Query
dev=# explain analyze select cha_type,sum(visits) from weekly_non_hstore a join seg1 b on a.ref_id=b.ref_id group by cha_type order by sum(visits) desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=3674073.37..3674431.16 rows=143115 width=27) (actual time=47520.637..47969.658 rows=3639539 loops=1)
Sort Key: (sum(a.visits))
Sort Method: quicksort Memory: 391723kB
-> HashAggregate (cost=3660386.70..3661817.85 rows=143115 width=27) (actual time=43655.637..44989.202 rows=3639539 loops=1)
-> Hash Join (cost=12029.58..3301286.54 rows=71820032 width=27) (actual time=209.789..26477.652 rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_non_hstore a (cost=0.00..1852856.32 rows=71820032 width=75) (actual time=0.053..8858.594 rows=71818882 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual time=209.189..209.189 rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual time=0.014..64.695 rows=371759 loops=1)
Total runtime: 48172.405 ms
(11 rows)
Time: 48173.569 ms
dev=# explain analyze select cha_type, sum(visits) from (select (each(visits)).key as cha_type,(each(visits)).value::numeric as visits from weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group by cha_type order by sum(visits) desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7599039.89..7599040.39 rows=200 width=64) (actual time=70424.561..70986.202 rows=3639539 loops=1)
Sort Key: (sum((((each(a.visits)).value)::numeric)))
Sort Method: quicksort Memory: 394779kB
-> HashAggregate (cost=7599030.24..7599032.24 rows=200 width=64) (actual time=59267.120..60502.647 rows=3639539 loops=1)
-> Hash Join (cost=12029.58..2022645.24 rows=371759000 width=184) (actual time=186.140..34619.879 rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_hstore a (cost=0.00..133321.14 rows=1292314 width=230) (actual time=0.107..416.741 rows=1292314 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual time=185.742..185.742 rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual time=0.016..62.123 rows=371759 loops=1)
Total runtime: 71177.675 ms
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance with hstore vs. non-hstore
@ 2014-09-01 06:21 Pavel Stehule <pavel.stehule@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2014-09-01 06:21 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-performance
Hi
In this use case hstore should not help .. there is relative high overhead
related with unpacking hstore -- so classic schema is better.
Hstore should not to replace well normalized schema - it should be a
replace for some semi normalized structures as EAV.
Hstore can have some profit from TOAST .. comprimation, less system data
overhead, but this advantage started from some length of data. You should
to see this benefit on table size. When table with HStore is less than
without, then there is benefit of Hstore. Last benefit of Hstore are
indexes over tuple (key, value) .. but you don't use it.
Regards
Pavel
2014-09-01 8:10 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:
> Hi ,
>
>
>
> I’m tweaking table layout to get better performance of query. One table
> doesn’t use hstore but expand all metrics of cha_type to different rows.
> The other table has hstore for metrics column as cha_type->metrics so it
> has less records than the first one.
>
>
>
> I would be expecting the query on seconds table has better performance
> than the first one. However, it’s not the case at all. I’m wondering if
> there’s something wrong with my execution plan? With the hstore table, the
> optimizer has totally wrong estimation on row counts at hash aggregate
> stage and it takes 34 seconds on hash-join,25 seconds on hash-aggregate, 10
> seconds on sort. However, with non-hstore table, it takes 17 seconds on
> hash join, 18 seconds on hashaggregate and 2 seconds on sort.
>
>
>
> Can someone help me to explain why this is happening? And is there a way
> to fine-tune the query?
>
>
>
> Table structure
>
>
>
> dev=# \d+ weekly_non_hstore
>
> Table "test.weekly_non_hstore"
>
> Column | Type | Modifiers | Storage | Stats target |
> Description
>
>
> ----------+------------------------+-----------+----------+--------------+-------------
>
> date | date | | plain | |
>
> ref_id | character varying(256) | | extended | |
>
> cha_typel | text | | extended | |
>
> visits | double precision | | plain | |
>
> pages | double precision | | plain | |
>
> duration | double precision | | plain | |
>
> Has OIDs: no
>
> Tablespace: "tbs_data"
>
>
>
> dev=# \d+ weekly_hstore
>
> Table "test.weekly_hstore"
>
> Column | Type | Modifiers | Storage | Stats target |
> Description
>
>
> ----------+------------------------+-----------+----------+--------------+-------------
>
> date | date | | plain | |
>
> ref_id | character varying(256) | | extended | |
>
> visits | hstore | | extended | |
>
> pages | hstore | | extended | |
>
> duration | hstore | | extended | |
>
> Has OIDs: no
>
> Tablespace: "tbs_data"
>
>
>
> dev=# select count(*) from weekly_non_hstore;
>
> count
>
> ----------
>
> 71818882
>
> (1 row)
>
>
>
>
>
> dev=# select count(*) from weekly_hstore;
>
> count
>
> ---------
>
> 1292314
>
> (1 row)
>
>
>
>
>
> Query
>
> dev=# explain analyze select cha_type,sum(visits) from weekly_non_hstore
> a join seg1 b on a.ref_id=b.ref_id group by cha_type order by sum(visits)
> desc;
>
>
> QUERY PLAN
>
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=3674073.37..3674431.16 rows=143115 width=27) (actual
> time=47520.637..47969.658 rows=3639539 loops=1)
>
> Sort Key: (sum(a.visits))
>
> Sort Method: quicksort Memory: 391723kB
>
> -> HashAggregate (cost=3660386.70..3661817.85 rows=143115 width=27)
> (actual time=43655.637..44989.202 rows=3639539 loops=1)
>
> -> Hash Join (cost=12029.58..3301286.54 rows=71820032 width=27)
> (actual time=209.789..26477.652 rows=36962761 loops=1)
>
> Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
>
> -> Seq Scan on weekly_non_hstore a (cost=0.00..1852856.32
> rows=71820032 width=75) (actual time=0.053..8858.594 rows=71818882 loops=1)
>
> -> Hash (cost=7382.59..7382.59 rows=371759 width=47)
> (actual time=209.189..209.189 rows=371759 loops=1)
>
> Buckets: 65536 Batches: 1 Memory Usage: 28951kB
>
> -> Seq Scan on seg1 b (cost=0.00..7382.59
> rows=371759 width=47) (actual time=0.014..64.695 rows=371759 loops=1)
>
> Total runtime: 48172.405 ms
>
> (11 rows)
>
>
>
> Time: 48173.569 ms
>
>
>
> dev=# explain analyze select cha_type, sum(visits) from (select
> (each(visits)).key as cha_type,(each(visits)).value::numeric as visits from
> weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group by cha_type
> order by sum(visits) desc;
>
> QUERY
> PLAN
>
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=7599039.89..7599040.39 rows=200 width=64) (actual
> time=70424.561..70986.202 rows=3639539 loops=1)
>
> Sort Key: (sum((((each(a.visits)).value)::numeric)))
>
> Sort Method: quicksort Memory: 394779kB
>
> -> HashAggregate (cost=7599030.24..7599032.24 rows=200 width=64)
> (actual time=59267.120..60502.647 rows=3639539 loops=1)
>
> -> Hash Join (cost=12029.58..2022645.24 rows=371759000
> width=184) (actual time=186.140..34619.879 rows=36962761 loops=1)
>
> Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
>
> -> Seq Scan on weekly_hstore a (cost=0.00..133321.14
> rows=1292314 width=230) (actual time=0.107..416.741 rows=1292314 loops=1)
>
> -> Hash (cost=7382.59..7382.59 rows=371759 width=47)
> (actual time=185.742..185.742 rows=371759 loops=1)
>
> Buckets: 65536 Batches: 1 Memory Usage: 28951kB
>
> -> Seq Scan on seg1 b (cost=0.00..7382.59
> rows=371759 width=47) (actual time=0.016..62.123 rows=371759 loops=1)
>
> Total runtime: 71177.675 ms
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance with hstore vs. non-hstore
@ 2014-09-01 06:54 Huang, Suya <Suya.Huang@au.experian.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Huang, Suya @ 2014-09-01 06:54 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
Thank you Pavel.
The cost of unpacking hstore comparing to non-hstore could be calculated by:
Seq scan on hstore table + hash join with seg1 table:
Hstore: 416.741+ 34619.879 =~34 seconds
Non-hstore: 8858.594 +26477.652 =~ 34 seconds
The subsequent hash-aggregate and sort operation should be working on the unpacked hstore rows which has same row counts as non-hstore table. however, timing on those operations actually makes the big difference.
I don’t quite get why…
Thanks,
Suya
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Monday, September 01, 2014 4:22 PM
To: Huang, Suya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] query performance with hstore vs. non-hstore
Hi
In this use case hstore should not help .. there is relative high overhead related with unpacking hstore -- so classic schema is better.
Hstore should not to replace well normalized schema - it should be a replace for some semi normalized structures as EAV.
Hstore can have some profit from TOAST .. comprimation, less system data overhead, but this advantage started from some length of data. You should to see this benefit on table size. When table with HStore is less than without, then there is benefit of Hstore. Last benefit of Hstore are indexes over tuple (key, value) .. but you don't use it.
Regards
Pavel
2014-09-01 8:10 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com<mailto:Suya.Huang@au.experian.com>>:
Hi ,
I’m tweaking table layout to get better performance of query. One table doesn’t use hstore but expand all metrics of cha_type to different rows. The other table has hstore for metrics column as cha_type->metrics so it has less records than the first one.
I would be expecting the query on seconds table has better performance than the first one. However, it’s not the case at all. I’m wondering if there’s something wrong with my execution plan? With the hstore table, the optimizer has totally wrong estimation on row counts at hash aggregate stage and it takes 34 seconds on hash-join,25 seconds on hash-aggregate, 10 seconds on sort. However, with non-hstore table, it takes 17 seconds on hash join, 18 seconds on hashaggregate and 2 seconds on sort.
Can someone help me to explain why this is happening? And is there a way to fine-tune the query?
Table structure
dev=# \d+ weekly_non_hstore
Table "test.weekly_non_hstore"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------------------------+-----------+----------+--------------+-------------
date | date | | plain | |
ref_id | character varying(256) | | extended | |
cha_typel | text | | extended | |
visits | double precision | | plain | |
pages | double precision | | plain | |
duration | double precision | | plain | |
Has OIDs: no
Tablespace: "tbs_data"
dev=# \d+ weekly_hstore
Table "test.weekly_hstore"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------------------------+-----------+----------+--------------+-------------
date | date | | plain | |
ref_id | character varying(256) | | extended | |
visits | hstore | | extended | |
pages | hstore | | extended | |
duration | hstore | | extended | |
Has OIDs: no
Tablespace: "tbs_data"
dev=# select count(*) from weekly_non_hstore;
count
----------
71818882
(1 row)
dev=# select count(*) from weekly_hstore;
count
---------
1292314
(1 row)
Query
dev=# explain analyze select cha_type,sum(visits) from weekly_non_hstore a join seg1 b on a.ref_id=b.ref_id group by cha_type order by sum(visits) desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=3674073.37..3674431.16 rows=143115 width=27) (actual time=47520.637..47969.658 rows=3639539 loops=1)
Sort Key: (sum(a.visits))
Sort Method: quicksort Memory: 391723kB
-> HashAggregate (cost=3660386.70..3661817.85 rows=143115 width=27) (actual time=43655.637..44989.202 rows=3639539 loops=1)
-> Hash Join (cost=12029.58..3301286.54 rows=71820032 width=27) (actual time=209.789..26477.652 rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_non_hstore a (cost=0.00..1852856.32 rows=71820032 width=75) (actual time=0.053..8858.594 rows=71818882 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual time=209.189..209.189 rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual time=0.014..64.695 rows=371759 loops=1)
Total runtime: 48172.405 ms
(11 rows)
Time: 48173.569 ms
dev=# explain analyze select cha_type, sum(visits) from (select (each(visits)).key as cha_type,(each(visits)).value::numeric as visits from weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group by cha_type order by sum(visits) desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7599039.89..7599040.39 rows=200 width=64) (actual time=70424.561..70986.202 rows=3639539 loops=1)
Sort Key: (sum((((each(a.visits)).value)::numeric)))
Sort Method: quicksort Memory: 394779kB
-> HashAggregate (cost=7599030.24..7599032.24 rows=200 width=64) (actual time=59267.120..60502.647 rows=3639539 loops=1)
-> Hash Join (cost=12029.58..2022645.24 rows=371759000 width=184) (actual time=186.140..34619.879 rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_hstore a (cost=0.00..133321.14 rows=1292314 width=230) (actual time=0.107..416.741 rows=1292314 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual time=185.742..185.742 rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual time=0.016..62.123 rows=371759 loops=1)
Total runtime: 71177.675 ms
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance with hstore vs. non-hstore
@ 2014-09-01 07:06 Pavel Stehule <pavel.stehule@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2014-09-01 07:06 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-performance
2014-09-01 8:54 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:
> Thank you Pavel.
>
>
>
> The cost of unpacking hstore comparing to non-hstore could be calculated
> by:
>
> Seq scan on hstore table + hash join with seg1 table:
>
> Hstore: 416.741+ 34619.879 =~34 seconds
>
> Non-hstore: 8858.594 +26477.652 =~ 34 seconds
>
>
>
> The subsequent hash-aggregate and sort operation should be working on the
> unpacked hstore rows which has same row counts as non-hstore table.
> however, timing on those operations actually makes the big difference.
>
>
> I don’t quite get why…
>
These values can be messy -- timing in EXPLAIN ANALYZE has relative big
impact but different for some methods
try to watch complete time for EXPLAIN (ANALYZE, TIMING OFF)
>
>
> Thanks,
>
> Suya
>
>
>
> *From:* Pavel Stehule [mailto:pavel.stehule@gmail.com]
> *Sent:* Monday, September 01, 2014 4:22 PM
> *To:* Huang, Suya
> *Cc:* pgsql-performance@postgresql.org
> *Subject:* Re: [PERFORM] query performance with hstore vs. non-hstore
>
>
>
> Hi
>
> In this use case hstore should not help .. there is relative high overhead
> related with unpacking hstore -- so classic schema is better.
>
> Hstore should not to replace well normalized schema - it should be a
> replace for some semi normalized structures as EAV.
>
> Hstore can have some profit from TOAST .. comprimation, less system data
> overhead, but this advantage started from some length of data. You should
> to see this benefit on table size. When table with HStore is less than
> without, then there is benefit of Hstore. Last benefit of Hstore are
> indexes over tuple (key, value) .. but you don't use it.
>
> Regards
>
> Pavel
>
>
>
> 2014-09-01 8:10 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:
>
> Hi ,
>
>
>
> I’m tweaking table layout to get better performance of query. One table
> doesn’t use hstore but expand all metrics of cha_type to different rows.
> The other table has hstore for metrics column as cha_type->metrics so it
> has less records than the first one.
>
>
>
> I would be expecting the query on seconds table has better performance
> than the first one. However, it’s not the case at all. I’m wondering if
> there’s something wrong with my execution plan? With the hstore table, the
> optimizer has totally wrong estimation on row counts at hash aggregate
> stage and it takes 34 seconds on hash-join,25 seconds on hash-aggregate, 10
> seconds on sort. However, with non-hstore table, it takes 17 seconds on
> hash join, 18 seconds on hashaggregate and 2 seconds on sort.
>
>
>
> Can someone help me to explain why this is happening? And is there a way
> to fine-tune the query?
>
>
>
> Table structure
>
>
>
> dev=# \d+ weekly_non_hstore
>
> Table "test.weekly_non_hstore"
>
> Column | Type | Modifiers | Storage | Stats target |
> Description
>
>
> ----------+------------------------+-----------+----------+--------------+-------------
>
> date | date | | plain | |
>
> ref_id | character varying(256) | | extended | |
>
> cha_typel | text | | extended | |
>
> visits | double precision | | plain | |
>
> pages | double precision | | plain | |
>
> duration | double precision | | plain | |
>
> Has OIDs: no
>
> Tablespace: "tbs_data"
>
>
>
> dev=# \d+ weekly_hstore
>
> Table "test.weekly_hstore"
>
> Column | Type | Modifiers | Storage | Stats target |
> Description
>
>
> ----------+------------------------+-----------+----------+--------------+-------------
>
> date | date | | plain | |
>
> ref_id | character varying(256) | | extended | |
>
> visits | hstore | | extended | |
>
> pages | hstore | | extended | |
>
> duration | hstore | | extended | |
>
> Has OIDs: no
>
> Tablespace: "tbs_data"
>
>
>
> dev=# select count(*) from weekly_non_hstore;
>
> count
>
> ----------
>
> 71818882
>
> (1 row)
>
>
>
>
>
> dev=# select count(*) from weekly_hstore;
>
> count
>
> ---------
>
> 1292314
>
> (1 row)
>
>
>
>
>
> Query
>
> dev=# explain analyze select cha_type,sum(visits) from weekly_non_hstore
> a join seg1 b on a.ref_id=b.ref_id group by cha_type order by sum(visits)
> desc;
>
>
> QUERY PLAN
>
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=3674073.37..3674431.16 rows=143115 width=27) (actual
> time=47520.637..47969.658 rows=3639539 loops=1)
>
> Sort Key: (sum(a.visits))
>
> Sort Method: quicksort Memory: 391723kB
>
> -> HashAggregate (cost=3660386.70..3661817.85 rows=143115 width=27)
> (actual time=43655.637..44989.202 rows=3639539 loops=1)
>
> -> Hash Join (cost=12029.58..3301286.54 rows=71820032 width=27)
> (actual time=209.789..26477.652 rows=36962761 loops=1)
>
> Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
>
> -> Seq Scan on weekly_non_hstore a (cost=0.00..1852856.32
> rows=71820032 width=75) (actual time=0.053..8858.594 rows=71818882 loops=1)
>
> -> Hash (cost=7382.59..7382.59 rows=371759 width=47)
> (actual time=209.189..209.189 rows=371759 loops=1)
>
> Buckets: 65536 Batches: 1 Memory Usage: 28951kB
>
> -> Seq Scan on seg1 b (cost=0.00..7382.59
> rows=371759 width=47) (actual time=0.014..64.695 rows=371759 loops=1)
>
> Total runtime: 48172.405 ms
>
> (11 rows)
>
>
>
> Time: 48173.569 ms
>
>
>
> dev=# explain analyze select cha_type, sum(visits) from (select
> (each(visits)).key as cha_type,(each(visits)).value::numeric as visits from
> weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group by cha_type
> order by sum(visits) desc;
>
> QUERY
> PLAN
>
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=7599039.89..7599040.39 rows=200 width=64) (actual
> time=70424.561..70986.202 rows=3639539 loops=1)
>
> Sort Key: (sum((((each(a.visits)).value)::numeric)))
>
> Sort Method: quicksort Memory: 394779kB
>
> -> HashAggregate (cost=7599030.24..7599032.24 rows=200 width=64)
> (actual time=59267.120..60502.647 rows=3639539 loops=1)
>
> -> Hash Join (cost=12029.58..2022645.24 rows=371759000
> width=184) (actual time=186.140..34619.879 rows=36962761 loops=1)
>
> Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
>
> -> Seq Scan on weekly_hstore a (cost=0.00..133321.14
> rows=1292314 width=230) (actual time=0.107..416.741 rows=1292314 loops=1)
>
> -> Hash (cost=7382.59..7382.59 rows=371759 width=47)
> (actual time=185.742..185.742 rows=371759 loops=1)
>
> Buckets: 65536 Batches: 1 Memory Usage: 28951kB
>
> -> Seq Scan on seg1 b (cost=0.00..7382.59
> rows=371759 width=47) (actual time=0.016..62.123 rows=371759 loops=1)
>
> Total runtime: 71177.675 ms
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance with hstore vs. non-hstore
@ 2014-09-02 00:53 Huang, Suya <Suya.Huang@au.experian.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Huang, Suya @ 2014-09-02 00:53 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
Hi Pavel,
See output of explain (analyze,timing off), the total runtime is close to the one enable timing.
dev=# EXPLAIN (ANALYZE, TIMING OFF) select cha_type,sum(visits) from weekly_non_hstore a join seg1 b on a.ref_id=b.ref_id group by cha_type order by sum(visits) desc;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=3674118.09..3674476.91 rows=143528 width=27) (actual rows=3639539 loops=1)
Sort Key: (sum(a.visits))
Sort Method: quicksort Memory: 391723kB
-> HashAggregate (cost=3660388.94..3661824.22 rows=143528 width=27) (actual rows=3639539 loops=1)
-> Hash Join (cost=12029.58..3301288.46 rows=71820096 width=27) (actual rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_non_hstore a (cost=0.00..1852856.96 rows=71820096 width=75) (actual rows=71818882 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual rows=371759 loops=1)
Total runtime: 42914.194 ms
(11 rows)
dev=# explain (analyze, timing off) select cha_type, sum(visits) from (select (each(visits)).key as cha_type,(each(visits)).value::numeric as visits from weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group by cha_type order by sum(visits) desc;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7599039.89..7599040.39 rows=200 width=64) (actual rows=3639539 loops=1)
Sort Key: (sum((((each(a.visits)).value)::numeric)))
Sort Method: quicksort Memory: 394779kB
-> HashAggregate (cost=7599030.24..7599032.24 rows=200 width=64) (actual rows=3639539 loops=1)
-> Hash Join (cost=12029.58..2022645.24 rows=371759000 width=186) (actual rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_hstore a (cost=0.00..133321.14 rows=1292314 width=232) (actual rows=1292314 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual rows=371759 loops=1)
Total runtime: 69521.570 ms
(11 rows)
Thanks,
Suya
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Monday, September 01, 2014 5:07 PM
To: Huang, Suya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] query performance with hstore vs. non-hstore
2014-09-01 8:54 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com<mailto:Suya.Huang@au.experian.com>>:
Thank you Pavel.
The cost of unpacking hstore comparing to non-hstore could be calculated by:
Seq scan on hstore table + hash join with seg1 table:
Hstore: 416.741+ 34619.879 =~34 seconds
Non-hstore: 8858.594 +26477.652 =~ 34 seconds
The subsequent hash-aggregate and sort operation should be working on the unpacked hstore rows which has same row counts as non-hstore table. however, timing on those operations actually makes the big difference.
I don’t quite get why…
These values can be messy -- timing in EXPLAIN ANALYZE has relative big impact but different for some methods
try to watch complete time for EXPLAIN (ANALYZE, TIMING OFF)
Thanks,
Suya
From: Pavel Stehule [mailto:pavel.stehule@gmail.com<mailto:pavel.stehule@gmail.com>]
Sent: Monday, September 01, 2014 4:22 PM
To: Huang, Suya
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] query performance with hstore vs. non-hstore
Hi
In this use case hstore should not help .. there is relative high overhead related with unpacking hstore -- so classic schema is better.
Hstore should not to replace well normalized schema - it should be a replace for some semi normalized structures as EAV.
Hstore can have some profit from TOAST .. comprimation, less system data overhead, but this advantage started from some length of data. You should to see this benefit on table size. When table with HStore is less than without, then there is benefit of Hstore. Last benefit of Hstore are indexes over tuple (key, value) .. but you don't use it.
Regards
Pavel
2014-09-01 8:10 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com<mailto:Suya.Huang@au.experian.com>>:
Hi ,
I’m tweaking table layout to get better performance of query. One table doesn’t use hstore but expand all metrics of cha_type to different rows. The other table has hstore for metrics column as cha_type->metrics so it has less records than the first one.
I would be expecting the query on seconds table has better performance than the first one. However, it’s not the case at all. I’m wondering if there’s something wrong with my execution plan? With the hstore table, the optimizer has totally wrong estimation on row counts at hash aggregate stage and it takes 34 seconds on hash-join,25 seconds on hash-aggregate, 10 seconds on sort. However, with non-hstore table, it takes 17 seconds on hash join, 18 seconds on hashaggregate and 2 seconds on sort.
Can someone help me to explain why this is happening? And is there a way to fine-tune the query?
Table structure
dev=# \d+ weekly_non_hstore
Table "test.weekly_non_hstore"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------------------------+-----------+----------+--------------+-------------
date | date | | plain | |
ref_id | character varying(256) | | extended | |
cha_typel | text | | extended | |
visits | double precision | | plain | |
pages | double precision | | plain | |
duration | double precision | | plain | |
Has OIDs: no
Tablespace: "tbs_data"
dev=# \d+ weekly_hstore
Table "test.weekly_hstore"
Column | Type | Modifiers | Storage | Stats target | Description
----------+------------------------+-----------+----------+--------------+-------------
date | date | | plain | |
ref_id | character varying(256) | | extended | |
visits | hstore | | extended | |
pages | hstore | | extended | |
duration | hstore | | extended | |
Has OIDs: no
Tablespace: "tbs_data"
dev=# select count(*) from weekly_non_hstore;
count
----------
71818882
(1 row)
dev=# select count(*) from weekly_hstore;
count
---------
1292314
(1 row)
Query
dev=# explain analyze select cha_type,sum(visits) from weekly_non_hstore a join seg1 b on a.ref_id=b.ref_id group by cha_type order by sum(visits) desc;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=3674073.37..3674431.16 rows=143115 width=27) (actual time=47520.637..47969.658 rows=3639539 loops=1)
Sort Key: (sum(a.visits))
Sort Method: quicksort Memory: 391723kB
-> HashAggregate (cost=3660386.70..3661817.85 rows=143115 width=27) (actual time=43655.637..44989.202 rows=3639539 loops=1)
-> Hash Join (cost=12029.58..3301286.54 rows=71820032 width=27) (actual time=209.789..26477.652 rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_non_hstore a (cost=0.00..1852856.32 rows=71820032 width=75) (actual time=0.053..8858.594 rows=71818882 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual time=209.189..209.189 rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual time=0.014..64.695 rows=371759 loops=1)
Total runtime: 48172.405 ms
(11 rows)
Time: 48173.569 ms
dev=# explain analyze select cha_type, sum(visits) from (select (each(visits)).key as cha_type,(each(visits)).value::numeric as visits from weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group by cha_type order by sum(visits) desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7599039.89..7599040.39 rows=200 width=64) (actual time=70424.561..70986.202 rows=3639539 loops=1)
Sort Key: (sum((((each(a.visits)).value)::numeric)))
Sort Method: quicksort Memory: 394779kB
-> HashAggregate (cost=7599030.24..7599032.24 rows=200 width=64) (actual time=59267.120..60502.647 rows=3639539 loops=1)
-> Hash Join (cost=12029.58..2022645.24 rows=371759000 width=184) (actual time=186.140..34619.879 rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_hstore a (cost=0.00..133321.14 rows=1292314 width=230) (actual time=0.107..416.741 rows=1292314 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual time=185.742..185.742 rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual time=0.016..62.123 rows=371759 loops=1)
Total runtime: 71177.675 ms
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance with hstore vs. non-hstore
@ 2014-09-02 03:38 David G Johnston <david.g.johnston@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G Johnston @ 2014-09-02 03:38 UTC (permalink / raw)
To: pgsql-performance
Huang, Suya wrote
> See output of explain (analyze,timing off), the total runtime is close to
> the one enable timing.
Calling 43s "close to" 70s doesn't sound right...
> dev=# explain (analyze, timing off) select cha_type, sum(visits) from
> (select (each(visits)).key as cha_type,(each(visits)).value::numeric as
> visits from weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group
> by cha_type order by sum(visits) desc;
What version of PostgreSQL are you using?
Two calls to each() and cast to numeric are not free.
Your sequential scan savings is nearly 9 seconds but you lose all of that,
and more, when PostgreSQL evaluates the result of the scan and has to
process the each() and the cast before it performs the join against the
expanded result. There is no planner node for this activity but it does
cost time - in this case more time than it would take to simply store the
native data types in separate rows.
You really should expand the hstore after the join (i.e., in the top-most
select-list) but in this case since the join removed hardly any rows the
gain from doing so would be minimal. The idea being you should not expand
the hstore of any row that fails the join condition since it will not end up
in the final result anyway.
Also, in this specific case, the call to each(...).key is pointless - you
never use the data.
If you did need to use both columns, and are using 9.3, you should re-write
this to use LATERAL.
In 9.2- you, possibly using a CTE, could do something like this:
SELECT (each).* FROM (
SELECT each(hs) FROM ( VALUES('k=>1'::hstore) ) h (hs)
) src
This is a single call to each(), in a subquery, which result is then
expanded using (col).* notation in the parent query. This avoids calling
each twice - and note that (each(...).*) does not work to avoid the
double-call - you have to use a subquery / a CTE one to ensure that it is
not collapsed (offset 0 should work too but I find the CTE one a little
cleaner personally).
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/query-performance-with-hstore-vs-non-hstore-tp5817109p581728...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
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] 7+ messages in thread
* Re: query performance with hstore vs. non-hstore
@ 2014-09-04 07:33 Huang, Suya <Suya.Huang@au.experian.com>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Huang, Suya @ 2014-09-04 07:33 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; pgsql-performance
Hi David,
Thanks for the reply.
>Calling 43s "close to" 70s doesn't sound right...
Oops, I'm not saying 43s close to 70s... I mean that the plan generated by disable timing for explain plan doesn't make obvious difference comparing to the earlier plan I sent out which enabled timing.
>What version of PostgreSQL are you using?
>
>Two calls to each() and cast to numeric are not free.
>
>Your sequential scan savings is nearly 9 seconds but you lose all of that, and more, when PostgreSQL evaluates the result of the scan and has to process the each() and >the cast before it performs the join against the expanded result. There is no planner node for this activity but it does cost time - in this case more time than it >would take to simply store the native data types in separate rows.
>
>You really should expand the hstore after the join (i.e., in the top-most
>select-list) but in this case since the join removed hardly any rows the gain from doing so would be minimal. The idea being you should not expand the hstore of any row >that fails the join condition since it will not end up in the final result anyway.
>
>Also, in this specific case, the call to each(...).key is pointless - you never use the data.
>
>If you did need to use both columns, and are using 9.3, you should re-write this to use LATERAL.
>
>In 9.2- you, possibly using a CTE, could do something like this:
>
>SELECT (each).* FROM (
>SELECT each(hs) FROM ( VALUES('k=>1'::hstore) ) h (hs)
>) src
>
>This is a single call to each(), in a subquery, which result is then expanded using (col).* notation in the parent query. This avoids calling each twice - and note that >(each(...).*) does not work to avoid the double-call - you have to use a subquery / a CTE one to ensure that it is not collapsed (offset 0 should work too but I find the >CTE one a little cleaner personally).
>
I'm using Postgresql 9.3.4.
I changed the query as you suggested. The execution time are still similar to the original one.
dev=# explain analyze select (each).key as cha_type, sum((each).value::numeric) as visits from (select each(visits) from weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo group by cha_type order by visits desc;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=9455046.69..9455047.19 rows=200 width=32) (actual time=70928.881..71425.833 rows=3639539 loops=1)
Sort Key: (sum(((foo.each).value)::numeric))
Sort Method: quicksort Memory: 394779kB
-> HashAggregate (cost=9455037.05..9455039.05 rows=200 width=32) (actual time=60077.937..61425.469 rows=3639539 loops=1)
-> Subquery Scan on foo (cost=12029.58..5737447.05 rows=371759000 width=32) (actual time=281.658..23912.400 rows=36962761 loops=1)
-> Hash Join (cost=12029.58..2019857.05 rows=371759000 width=186) (actual time=281.655..18759.265 rows=36962761 loops=1)
Hash Cond: ((a.ref_id)::text = (b.ref_id)::text)
-> Seq Scan on weekly_hstore a (cost=0.00..133321.14 rows=1292314 width=232) (actual time=11.141..857.959 rows=1292314 loops=1)
-> Hash (cost=7382.59..7382.59 rows=371759 width=47) (actual time=262.722..262.722 rows=371759 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 28951kB
-> Seq Scan on seg1 b (cost=0.00..7382.59 rows=371759 width=47) (actual time=11.701..113.859 rows=371759 loops=1)
Total runtime: 71626.871 ms
(12 rows)
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of David G Johnston
Sent: Tuesday, September 02, 2014 1:38 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] query performance with hstore vs. non-hstore
Huang, Suya wrote
> See output of explain (analyze,timing off), the total runtime is close
> to the one enable timing.
Calling 43s "close to" 70s doesn't sound right...
> dev=# explain (analyze, timing off) select cha_type, sum(visits) from
> (select (each(visits)).key as cha_type,(each(visits)).value::numeric
> as visits from weekly_hstore a join seg1 b on a.ref_id=b.ref_id )foo
> group by cha_type order by sum(visits) desc;
What version of PostgreSQL are you using?
Two calls to each() and cast to numeric are not free.
Your sequential scan savings is nearly 9 seconds but you lose all of that, and more, when PostgreSQL evaluates the result of the scan and has to process the each() and the cast before it performs the join against the expanded result. There is no planner node for this activity but it does cost time - in this case more time than it would take to simply store the native data types in separate rows.
You really should expand the hstore after the join (i.e., in the top-most
select-list) but in this case since the join removed hardly any rows the gain from doing so would be minimal. The idea being you should not expand the hstore of any row that fails the join condition since it will not end up in the final result anyway.
Also, in this specific case, the call to each(...).key is pointless - you never use the data.
If you did need to use both columns, and are using 9.3, you should re-write this to use LATERAL.
In 9.2- you, possibly using a CTE, could do something like this:
SELECT (each).* FROM (
SELECT each(hs) FROM ( VALUES('k=>1'::hstore) ) h (hs)
) src
This is a single call to each(), in a subquery, which result is then expanded using (col).* notation in the parent query. This avoids calling each twice - and note that (each(...).*) does not work to avoid the double-call - you have to use a subquery / a CTE one to ensure that it is not collapsed (offset 0 should work too but I find the CTE one a little cleaner personally).
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/query-performance-with-hstore-vs-non-hstore-tp5817109p581728...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
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] 7+ messages in thread
end of thread, other threads:[~2014-09-04 07:33 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-09-01 06:10 query performance with hstore vs. non-hstore Huang, Suya <Suya.Huang@au.experian.com>
2014-09-01 06:21 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-09-01 06:54 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-09-01 07:06 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-09-02 00:53 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-09-02 03:38 ` David G Johnston <david.g.johnston@gmail.com>
2014-09-04 07:33 ` Huang, Suya <Suya.Huang@au.experian.com>
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