Re: Optimize date query for large child tables: GiST or GIN?
| От | David Jarvis |
|---|---|
| Тема | Re: Optimize date query for large child tables: GiST or GIN? |
| Дата | |
| Msg-id | AANLkTilG5hKF_Ue_OWuinYN_2KGUH05qx4j4jAQERh2G@mail.gmail.com обсуждение исходный текст |
| Ответ на | Optimize date query for large child tables: GiST or GIN? (David Jarvis <thangalin@gmail.com>) |
| Список | pgsql-performance |
Hi,
This is what I ran:
As far as I can tell, it appears they are equivalent?
Either way, the cost for performing a GROUP BY is high (I ran once with extract and once with date_part). The date_part EXPLAIN ANALYSE resulted in:
"Limit (cost=1748024.65..1748028.65 rows=200 width=12) (actual time=65471.448..65471.542 rows=101 loops=1)"
The EXTRACT EXPLAIN ANALYSE came to:
"Limit (cost=1748024.65..1748028.65 rows=200 width=12) (actual time=44913.263..44913.330 rows=101 loops=1)"
If PG treats them differently, I'd like to know how so that I can do the right thing. As it is, I cannot see the difference in performance between date_part and EXTRACT.
Dave
No. It is not the same function, so Postgres has no way to know it produces the same results (if it does).CREATE INDEX measurement_01_001_y_idx
ON climate.measurement_01_001
USING btree
(date_part('year'::text, taken));
Is that equivalent to what you suggest?
CREATE INDEX
measurement_013_taken_year_idx
ON
climate.measurement_013
(EXTRACT( YEAR FROM taken ));
This is what pgadmin3 shows me:measurement_013_taken_year_idx
ON
climate.measurement_013
(EXTRACT( YEAR FROM taken ));
CREATE INDEX measurement_013_taken_year_idx
ON climate.measurement_013
USING btree
(date_part('year'::text, taken));
ON climate.measurement_013
USING btree
(date_part('year'::text, taken));
As far as I can tell, it appears they are equivalent?
Either way, the cost for performing a GROUP BY is high (I ran once with extract and once with date_part). The date_part EXPLAIN ANALYSE resulted in:
"Limit (cost=1748024.65..1748028.65 rows=200 width=12) (actual time=65471.448..65471.542 rows=101 loops=1)"
The EXTRACT EXPLAIN ANALYSE came to:
"Limit (cost=1748024.65..1748028.65 rows=200 width=12) (actual time=44913.263..44913.330 rows=101 loops=1)"
If PG treats them differently, I'd like to know how so that I can do the right thing. As it is, I cannot see the difference in performance between date_part and EXTRACT.
Dave
В списке pgsql-performance по дате отправления: