Timeline for Slow query in PostgreSQL selecting a single row from between a range defined in two columns
Current License: CC BY-SA 3.0
25 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 7, 2020 at 22:25 | history | edited | CommunityBot |
added [postgresql-performance] to 571 questions - Shog9 (Id=1924)
|
|
| Jan 7, 2020 at 22:23 | history | edited | CommunityBot |
added [query-performance] to 2412 questions - Shog9 (Id=1924)
|
|
| Nov 10, 2017 at 14:49 | comment | added | Parker |
@EvanCarrollQWERHJKL I did try using int4 instead of bigint and I get integer overflows: ERROR: value "2147483648" is out of range for type integer CONTEXT: COPY ip2location_db11, line 2071411, column ip_from: "2147483648"
|
|
| Nov 10, 2017 at 13:09 | vote | accept | Parker | ||
| Nov 10, 2017 at 12:45 | comment | added | Parker | @a_horse_with_no_name Done, see updated question. The timings start out ok, but then tend back toward 850ms. | |
| Nov 10, 2017 at 12:44 | history | edited | Parker | CC BY-SA 3.0 |
Added new query timings as requested in comment.
|
| Nov 10, 2017 at 11:11 | comment | added | user1822 |
That query with the multi-column index only takes 60ms on my Laptop with a SSD and Postgres 10. Can you run explain (analyze, verbose, buffers, timing) with the query?
|
|
| Nov 10, 2017 at 9:59 | answer | added | Kent Chenery | timeline score: 5 | |
| Nov 10, 2017 at 1:12 | history | tweeted | twitter.com/StackDBAs/status/928792357641838593 | ||
| Nov 9, 2017 at 19:02 | comment | added | Parker | @bma On the contrary, the method from that link was the right solution in my case. I don't mind doing a bit of extra work in the client code in order to avoid having to worry about managing extensions during database upgrades. | |
| Nov 9, 2017 at 18:43 | comment | added | bma | You're right about the bigint casting stuff. I should not have supplied that link, it is outdated (though I think it predated ip4r). | |
| Nov 9, 2017 at 17:55 | vote | accept | Parker | ||
| Nov 10, 2017 at 13:09 | |||||
| Nov 9, 2017 at 17:55 | comment | added | Parker | @EvanCarrollQWERHJKL Right, so there is another benefit of using your method. Prior to Java 8, there were no unsigned integers in Java. Your extension solution takes that completely out of the equation, by moving the problem from the client code to the database. | |
| Nov 9, 2017 at 17:48 | comment | added | Evan Carroll | Actually just casts the signed int to unsigned int github.com/RhodiumToad/ip4r/blob/… | |
| Nov 9, 2017 at 17:42 | comment | added | Evan Carroll | So the answer I provided has the proper methods to get to_ and from_ a signed int4 under the hood, and this method rather than doing that just throws things into a bigint. =) | |
| Nov 9, 2017 at 17:38 | comment | added | Parker | @EvanCarrollQWERHJKL I used the schema exactly as provided by the vendor. I believe a long was used in order to prevent negative values when converting between address and numeric representations. | |
| Nov 9, 2017 at 17:08 | comment | added | Evan Carroll | Why would you store ip4 in bigint to begin with? It fits fine in int4 | |
| Nov 9, 2017 at 15:33 | answer | added | Evan Carroll | timeline score: 1 | |
| Nov 9, 2017 at 15:26 | history | edited | Parker | CC BY-SA 3.0 |
Added sample rows from CSV.
|
| Nov 9, 2017 at 15:25 | comment | added | Parker | @EvanCarrollQWERHJKL These are IPv4 addresses. I'm using Java PreparedStatements for query, and supplying the query addresses already converted to 64-bit long. Three sample rows added to question. | |
| Nov 9, 2017 at 15:23 | comment | added | Evan Carroll | are these ipv4 or v6? can you add a row of data? | |
| Nov 9, 2017 at 14:28 | comment | added | Parker | @bma Perfect solution, thank you! This reduced the query time to 0.073ms. | |
| Nov 9, 2017 at 14:28 | answer | added | Parker | timeline score: 1 | |
| Nov 9, 2017 at 14:08 | comment | added | bma | There is an example of how I solved lookups using a gist index at siafoo.net/article/53#comment_288. That might be of interest to you, at least as a starting point. | |
| Nov 9, 2017 at 14:03 | history | asked | Parker | CC BY-SA 3.0 |