Timeline for Optimizing SELECT queries
Current License: CC BY-SA 3.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 7, 2020 at 22:17 | history | edited | CommunityBot |
added [query-optimization] to 86 questions - Shog9 (Id=1924)
|
|
| Mar 22, 2016 at 16:06 | answer | added | Matjaž | timeline score: 1 | |
| Nov 6, 2014 at 9:04 | comment | added | András Váczi |
Well, your table and the index on tag are very close in their size. Postgres simply decided that the sequential scan would be the cheaper. In the aggregate case, it's no wonder that aggregating 100M+ rows takes time. At the same time, it would be interesting to see the EXPLAIN (ANALYZE, BUFFERS) output to see the ratio between pages read from disk and pages cached.
|
|
| Nov 5, 2014 at 17:29 | history | tweeted | twitter.com/#!/StackDBAs/status/530049294925963264 | ||
| Nov 5, 2014 at 17:12 | comment | added | Kamil Gosciminski |
Did you run ANALYZE datapoints after creating index? Please try to CREATE INDEX idx_test_01 ON datapoints(tag, time) then ANALYZE datapoints and then EXPLAIN ... if you could.
|
|
| Nov 5, 2014 at 17:06 | comment | added | kolistivra | @ConsiderMe Still no luck =/ | |
| Nov 5, 2014 at 17:06 | history | edited | kolistivra | CC BY-SA 3.0 |
added 835 characters in body
|
| Nov 5, 2014 at 16:53 | comment | added | Kamil Gosciminski | This means, that the planner has chosen the right plan for your query (not using the index). Please create separate btree index on column time, analyze table and then run second query. Update with results of explain afterwards. | |
| Nov 5, 2014 at 16:44 | comment | added | kolistivra |
@ConsiderMe I tried turning off sequential scan to force using the index, but it didn't help. I did not create the time index after this, do you still suggest creating it?
|
|
| Nov 5, 2014 at 16:43 | history | edited | kolistivra | CC BY-SA 3.0 |
Tried turning of sequential scan
|
| Nov 5, 2014 at 16:36 | comment | added | Kamil Gosciminski |
Create separate index on time. The reason your DISTINCT tag is using Sequence scan might be because you have bigger percentage of distinct values comparing with those which are not so it wouldn't benefit from an index scan in this case.
|
|
| Nov 5, 2014 at 16:33 | history | asked | kolistivra | CC BY-SA 3.0 |