In my case I eventually need to select on this table along with another table, so it seems to make sense to create a subset of the table as a table of its own : CREATE TABLE subfoobar AS (SELECT * FROM foobar WHERE bar IN (SELECT DISTINCT bar FROM othertable) ORDER BY bar) > EXPLAIN (ANALYZE, BUFFERS) SELECT count(foo) OVER (PARTITION BY bar) FROM foobar The only one which helped or was not already active was to " rewrite the table in the physical order of the index", using CLUSTER (you may prefer pg_repack, read original post) : > CLUSTER foobar USING bar_idx I tried a few of the suggestions of this post - increasing statistics had no significant effect in my case. In my case I'm working on SSD, so switching to RAM (increasing work_mem to 1 GB) only reduces the processing time by 1.5 : > EXPLAIN (ANALYZE, BUFFERS) SELECT foo OVER (PARTITION BY bar) FROM foobar In most cases increasing work_mem, as suggested by hbn, should help. > EXPLAIN ANALYZE SELECT foo FROM foobar Sort Method: external merge Disk: 27176kB Here are the EXPLAIN ANALYZE for a simple query with and without PARTITION BY : > EXPLAIN ANALYZE SELECT count(foo) OVER (PARTITION BY bar) FROM foobar I tried to CREATE INDEX bar_idx ON foobar(bar) but it had no effect on performance (IRL there is already a primary key on another column of the table). I find PARTITION BY quite slow and am wondering if I can do anything to improve its performance ? Table contains ~1.7 million rows in total and about 15 rows for each distinct bar value. Column bar is almost ordered and rows of common bar value follow each other.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |