Timeline for PosgtreSQL GIN + BTree ordering
Current License: CC BY-SA 3.0
21 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 25, 2017 at 12:14 | vote | accept | malejpavouk | ||
| Nov 23, 2017 at 7:25 | comment | added | malejpavouk | @EvanCarroll the opposite ordering has almost the same results. | |
| Nov 23, 2017 at 7:21 | comment | added | malejpavouk | I have added compound index and removed indexes on single columns to motivate planner to use the compound index. It helped a bit for the original query, but 300s. With FROM subselect fence it is 183s, which is approximatelly the same as two separate indexes. | |
| Nov 22, 2017 at 22:58 | comment | added | malejpavouk | @EvanCarroll I will try tomorrow, its midnight here :-)...But I believe I saw today that PG was reporting both going through the index in forward and back direction (when I was fiddling with ASC/DESC). | |
| Nov 22, 2017 at 22:55 | comment | added | Evan Carroll |
@malejpavouk does it change the plan if you remove both of the DESC leaving it to ASC? Pg10 should be smarter than that anyway.
|
|
| Nov 22, 2017 at 22:53 | comment | added | malejpavouk | I have added description of the table. The hack with limit would work if the data in json was controlled by us, unfortunatelly those are generic customer data. And it /may/ happen that the field in json will have really low selectivity. And this would create a lot of headache to some unlucky guy trying to find the bug in few years, when the hack manifests itself... | |
| Nov 22, 2017 at 22:46 | comment | added | ypercubeᵀᴹ | And please use sane date literals. | |
| Nov 22, 2017 at 22:44 | comment | added | ypercubeᵀᴹ |
Anothe rhack idea would be to try the "fencing FROM" with LIMIT 10000 inside there. It's a hack and not guaranteed to always return 100 results, even if there more in the table of course. But still, it would be nice to see efficiency and what plan is produced.
|
|
| Nov 22, 2017 at 22:43 | comment | added | Evan Carroll |
@malejpavouk you still have a sort in there, do you have an index on stamp::timestamp without time zone? let's see the table \d
|
|
| Nov 22, 2017 at 22:40 | comment | added | ypercubeᵀᴹ | @malejpavouk did you try the composite gin index? I'm curious how it performs. Oh, we were cross-commenting ;) | |
| Nov 22, 2017 at 22:40 | comment | added | malejpavouk | I will try tomorrow the compound index - I had today some problems with it as we do not have this extension available on our installation (hence it throws some errors that control file is missing). Index on the field only would be a good solution, unfortunatelly we do not control what exactly is in those jsons :-((..those are customer specific data | |
| Nov 22, 2017 at 22:35 | comment | added | malejpavouk | + added explain analyze for the original query with disable bitmap scan, which works quite as expected (but is hacky).... | |
| Nov 22, 2017 at 22:29 | comment | added | malejpavouk | 200 seconds...behaves quite rationally when timerange is reduced, but still requires a lot of RAM and is much slower than original with disabled bitmap scan (takes 2 millis). I tried also subselect in where, but this had worse performance. | |
| Nov 22, 2017 at 21:31 | comment | added | Evan Carroll | @malejpavouk updated again, try that fence model. | |
| Nov 22, 2017 at 21:31 | history | edited | Evan Carroll | CC BY-SA 3.0 |
added 37 characters in body
|
| Nov 22, 2017 at 21:20 | comment | added | Evan Carroll | @malejpavouk updated, put the sort order inside the CTE and please update the plan. remove it from the outer. | |
| Nov 22, 2017 at 21:19 | history | edited | Evan Carroll | CC BY-SA 3.0 |
deleted 29 characters in body
|
| Nov 22, 2017 at 20:44 | history | edited | Evan Carroll | CC BY-SA 3.0 |
added 31 characters in body
|
| Nov 22, 2017 at 20:28 | comment | added | malejpavouk | I have tried the fencing approach (see edit) and the performance is similar to the original query... | |
| Nov 22, 2017 at 19:02 | history | edited | Evan Carroll | CC BY-SA 3.0 |
added 186 characters in body
|
| Nov 22, 2017 at 18:57 | history | answered | Evan Carroll | CC BY-SA 3.0 |