Reputation: 53
PostgreSQL and MongoDB WHERE IN condtions
Sorry for my bad English. I have a table in postgres like:
CREATE TABLE report
(
id serial primary key,
sensor_id integer,
datetime timestamp,
...(many other fields)
);
Collection in mongo stores the same data. Index in Postgres (I created all of them and separatly, but result is the same) :
create index report_datetime on report (datetime desc);
create index report_sensor_datetime on report (datetime desc, sensor_id);
create index report_sensor_datetime_1 on report (sensor_id, datetime desc);
Index in Mongo:
...createIndex({"sensor_id" : 1, "datetime" : -1})
Table\collection has > 100 millions records.
So, my question is why query in postgres like:
select *
from report
where sensor_id in (10234, 10236, 10250, 10251, 10253, 10255, 10262, 10263, 10264, 10265, 10267)
order by datetime desc
limit 100;
perfoms for many seconds (see explain below), but similar request in mongo:
db.getCollection('report').find({sensor_id: {$in: [
ObjectId("5f1833aa44be73ead6de88c9"),
ObjectId("5f1833ad44be73ead6de8977"),
ObjectId("5f18a0867c9195aa25ac0bb0"),
ObjectId("5f18a0867c9195aa25ac0bb5"),
ObjectId("5f18a0a47c9195aa25ac1241"),
ObjectId("5f1927b87c9195aa25b5203c"),
ObjectId("5f1a5f617c9195aa25e2d4df"),
ObjectId("5f1a5f647c9195aa25e2db3b"),
ObjectId("5f1a5f647c9195aa25e2db42"),
ObjectId("5f1a5f647c9195aa25e2db59"),
ObjectId("5f1a69027c9195aa25e4f84d")
]}}).sort({datetime:-1}).limit(100)
makes result for milliseconds?
How can I improve Postgres query or indexes for the same result? I don't belive that postgres can't do this query fast like mongo.
Mongo explain:
db.getCollection('report').find({sensor_id: {$in: [
ObjectId("5f1833aa44be73ead6de88c9"),
ObjectId("5f1833ad44be73ead6de8977"),
ObjectId("5f18a0867c9195aa25ac0bb0"),
ObjectId("5f18a0867c9195aa25ac0bb5"),
ObjectId("5f18a0a47c9195aa25ac1241"),
ObjectId("5f1927b87c9195aa25b5203c"),
ObjectId("5f1a5f617c9195aa25e2d4df"),
ObjectId("5f1a5f647c9195aa25e2db3b"),
ObjectId("5f1a5f647c9195aa25e2db42"),
ObjectId("5f1a5f647c9195aa25e2db59"),
ObjectId("5f1a69027c9195aa25e4f84d")
]}}).sort({datetime:-1}).limit(100).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "patrol.report",
"indexFilterSet" : false,
"parsedQuery" : {
"sensor_id" : {
"$in" : [
ObjectId("5f1833aa44be73ead6de88c9"),
ObjectId("5f1833ad44be73ead6de8977"),
ObjectId("5f18a0867c9195aa25ac0bb0"),
ObjectId("5f18a0867c9195aa25ac0bb5"),
ObjectId("5f18a0a47c9195aa25ac1241"),
ObjectId("5f1927b87c9195aa25b5203c"),
ObjectId("5f1a5f617c9195aa25e2d4df"),
ObjectId("5f1a5f647c9195aa25e2db3b"),
ObjectId("5f1a5f647c9195aa25e2db42"),
ObjectId("5f1a5f647c9195aa25e2db59"),
ObjectId("5f1a69027c9195aa25e4f84d")
]
}
},
"queryHash" : "C4968B0D",
"planCacheKey" : "58DC1ACB",
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 100,
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT_MERGE",
"sortPattern" : {
"datetime" : -1.0
},
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1833aa44be73ead6de88c9'), ObjectId('5f1833aa44be73ead6de88c9')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1833ad44be73ead6de8977'), ObjectId('5f1833ad44be73ead6de8977')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f18a0867c9195aa25ac0bb0'), ObjectId('5f18a0867c9195aa25ac0bb0')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f18a0867c9195aa25ac0bb5'), ObjectId('5f18a0867c9195aa25ac0bb5')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f18a0a47c9195aa25ac1241'), ObjectId('5f18a0a47c9195aa25ac1241')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1927b87c9195aa25b5203c'), ObjectId('5f1927b87c9195aa25b5203c')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1a5f617c9195aa25e2d4df'), ObjectId('5f1a5f617c9195aa25e2d4df')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1a5f647c9195aa25e2db3b'), ObjectId('5f1a5f647c9195aa25e2db3b')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1a5f647c9195aa25e2db42'), ObjectId('5f1a5f647c9195aa25e2db42')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1a5f647c9195aa25e2db59'), ObjectId('5f1a5f647c9195aa25e2db59')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : 1,
"datetime" : -1
},
"indexName" : "sensor_datetime",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : [],
"datetime" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1a69027c9195aa25e4f84d'), ObjectId('5f1a69027c9195aa25e4f84d')]"
],
"datetime" : [
"[MaxKey, MinKey]"
]
}
}
]
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"datetime" : -1.0
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"sensor_id" : -1
},
"indexName" : "sensor_id",
"isMultiKey" : false,
"multiKeyPaths" : {
"sensor_id" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"sensor_id" : [
"[ObjectId('5f1a69027c9195aa25e4f84d'), ObjectId('5f1a69027c9195aa25e4f84d')]",
"[ObjectId('5f1a5f647c9195aa25e2db59'), ObjectId('5f1a5f647c9195aa25e2db59')]",
"[ObjectId('5f1a5f647c9195aa25e2db42'), ObjectId('5f1a5f647c9195aa25e2db42')]",
"[ObjectId('5f1a5f647c9195aa25e2db3b'), ObjectId('5f1a5f647c9195aa25e2db3b')]",
"[ObjectId('5f1a5f617c9195aa25e2d4df'), ObjectId('5f1a5f617c9195aa25e2d4df')]",
"[ObjectId('5f1927b87c9195aa25b5203c'), ObjectId('5f1927b87c9195aa25b5203c')]",
"[ObjectId('5f18a0a47c9195aa25ac1241'), ObjectId('5f18a0a47c9195aa25ac1241')]",
"[ObjectId('5f18a0867c9195aa25ac0bb5'), ObjectId('5f18a0867c9195aa25ac0bb5')]",
"[ObjectId('5f18a0867c9195aa25ac0bb0'), ObjectId('5f18a0867c9195aa25ac0bb0')]",
"[ObjectId('5f1833ad44be73ead6de8977'), ObjectId('5f1833ad44be73ead6de8977')]",
"[ObjectId('5f1833aa44be73ead6de88c9'), ObjectId('5f1833aa44be73ead6de88c9')]"
]
}
}
}
}
}
]
},
"serverInfo" : {
"host" : "mongo",
"port" : 27017,
"version" : "4.2.12",
"gitVersion" : "5593fd8e33b60c75802edab304e23998fa0ce8a5"
},
"ok" : 1.0
}
Postgres explain:
explain (analyze, buffers)
select *
from report
where sensor_id in (10234, 10236, 10250, 10251, 10253, 10255, 10262, 10263, 10264, 10265, 10267)
order by datetime desc
limit 100;
Limit (cost=0.57..3984.27 rows=100 width=312) (actual time=147481.199..154733.025 rows=100 loops=1)
Buffers: shared hit=16330933 read=631496
-> Index Scan using report_datetime on report (cost=0.57..69625427.65 rows=1747758 width=312) (actual time=147481.195..154732.770 rows=100 loops=1)
Filter: (sensor_id = ANY ('{10234,10236,10250,10251,10253,10255,10262,10263,10264,10265,10267}'::integer[]))
Rows Removed by Filter: 17276154
Buffers: shared hit=16330933 read=631496
Planning:
Buffers: shared hit=128 read=3
Planning Time: 14.662 ms
Execution Time: 154733.156 ms
Additional question. When I run Postgres query with the same parameters it performs fast, but any add\remove\replace sensor_id will again slow. Mongo perfoms fast response on any params. For example, second Postgres query (but is also too slow):
explain (analyze, buffers)
select *
from report
where sensor_id in (10234, 10236, 10250, 10251, 10253, 10255, 10262, 10263, 10264, 10265, 10267)
order by datetime desc
limit 100;
Limit (cost=0.57..3984.27 rows=100 width=312) (actual time=22828.487..22949.028 rows=100 loops=1)
Buffers: shared hit=16932070 read=30359
-> Index Scan using report_datetime on report (cost=0.57..69625427.65 rows=1747758 width=312) (actual time=22828.483..22948.984 rows=100 loops=1)
Filter: (sensor_id = ANY ('{10234,10236,10250,10251,10253,10255,10262,10263,10264,10265,10267}'::integer[]))
Rows Removed by Filter: 17276154
Buffers: shared hit=16932070 read=30359
Planning Time: 0.253 ms
Execution Time: 22949.084 ms
Postgres conf (Mongo has default conf):
max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
Upvotes: 1
Views: 170
Answers (4)
Reputation: 44287
You can get a merge append in PostgreSQL if you write your query in a rather contorted way. It would be nice if writing it in a natural way also lead to this plan, but I guess MongoDB has to be better at something. Perhaps a future version of PostgreSQL will be better at this (especially if you work to make that happen).
(select * from report where sensor_id=10234 order by datetime desc limit 100)
UNION ALL
(select * from report where sensor_id=10236 order by datetime desc limit 100)
UNION ALL
(select * from report where sensor_id=10250 order by datetime desc limit 100)
UNION ALL
(select * from report where sensor_id=10251 order by datetime desc limit 100)
/* ... */
order by datetime desc limit 100
The LIMIT inside each select are only needed to convince the planner to choose the correct plan (in my hands, sometimes it gets the correct plan anyway but sometimes it does not). They should have no operational effect, as the outside LIMIT should always get hit first and/or at the same time. I've tested with simulated data as I do not have the bandwidth to download a 10GB file. Maybe you could compress it with pxz or something.
Upvotes: 0
Reputation: 11581
Alright. Data is loaded.
For the query "WHERE sensor_id=constant ORDER BY datetime DESC" postgres can pick the optimal plan, which is to scan the index on (sensor_id,datetime desc) which gives the rows in the correct order, which means 100 rows can be fetched, then it stops due to the LIMIT clause, and it's done, it won't fetch more rows.
But for the query "WHERE sensor_id IN (several values) ORDER BY datetime DESC" it doesn't know how to do that, so it will fetch all rows for the required sensor_ids, then sort them, and apply the LIMIT. This means it will fetch much more rows than necessary, which means lots of IO, plus sorting takes time.
So the trick is to make it use the first (fast) plan by giving it a query where it can apply it. But this query can only process one value of sensor_id, so let's stick it in a loop to process all values in the list:
explain -- (analyze, buffers)
select *
from (VALUES (87),(116),(71),(68),(51),(70),(52),(80),(132),(84),(92),(101)) sids
CROSS JOIN LATERAL (SELECT * FROM report WHERE sensor_id=sids.column1 ORDER BY datetime DESC LIMIT 100) o
ORDER BY datetime DESC limit 100;
The LATERAL join type makes the join subquery dependent on the previous tables, which here are just VALUES, but could be anything. This means the subquery will be executed for each row from VALUES, which is exactly what is needed here. For each sensor_id, it'll fetch the 100 most recent entries by datetime desc.
Note the LIMIT has to be copied inside this dependent subquery. Postgres won't do it on its own yet. But we know that if the whole result has "ORDER BY x LIMIT 100" then each subquery can also be "ORDER BY x LIMIT 100" and there won't be missing rows.
So this gives the following plan which is pretty fast:
Limit (cost=3842.00..3842.25 rows=100 width=81) (actual time=16.205..16.241 rows=100 loops=1)
Buffers: shared hit=163 read=1069
-> Sort (cost=3842.00..3845.00 rows=1200 width=81) (actual time=16.204..16.224 rows=100 loops=1)
Sort Key: report.datetime DESC
Sort Method: top-N heapsort Memory: 53kB
Buffers: shared hit=163 read=1069
-> Nested Loop (cost=0.57..3796.14 rows=1200 width=81) (actual time=0.059..15.051 rows=1200 loops=1)
Buffers: shared hit=163 read=1069
-> Values Scan on "*VALUES*" (cost=0.00..0.15 rows=12 width=4) (actual time=0.001..0.028 rows=12 loops=1)
-> Limit (cost=0.57..314.33 rows=100 width=77) (actual time=0.031..1.184 rows=100 loops=12)
Buffers: shared hit=163 read=1069
-> Index Scan using report_datetime_1 on report (cost=0.57..2575446.78 rows=820820 width=77) (actual time=0.029..1.151 rows=100 loops=12)
Index Cond: (sensor_id = "*VALUES*".column1)
Buffers: shared hit=163 read=1069
Planning Time: 0.238 ms
Execution Time: 16.321 ms
It is slower the first time the query is executed (next time takes 3ms) because the cache isn't hot, so it has to do some IO to fetch heap pages: "Buffers: shared hit=163 read=1069" -- the important part is it had to read 1069 buffers, so 1069 random IOs. On a SSD that's okay, but on a harddisk that does 100 iops on random access, it would take 10 seconds.
This is a bit of a waste since it will throw away most of the rows. Each subquery returns 100 rows, I put 12 values for sensor_id, so 1200 rows will be fetched, but only 100 will be kept after the final sort, which means 1100 heap fetch IO wasted. It would be better to do an index only scan.
I suppose (sensor_id, timestamp) is more or less unique, so let's fetch just that instead, then fetch the full rows only after the final LIMIT is applied...
WITH p AS (
SELECT sensor_id, datetime FROM (
VALUES (87),(116),(71),(68),(51),(70),(52),(80),(132),(84),(92),(101)) sids
CROSS JOIN LATERAL (
SELECT sensor_id,datetime FROM report WHERE sensor_id=sids.column1 ORDER BY datetime DESC LIMIT 100) o
ORDER BY datetime DESC limit 100)
SELECT r.* FROM p JOIN report r USING (sensor_id,datetime)
ORDER BY datetime DESC limit 100;
This does an index only scan in the subquery, which significantly reduces the amount of IO.
Upvotes: 1
Reputation: 247215
The difference in query execution time is because the first execution has to read way more 8kB blocks from disk: compare shared read=631496 and shared read=30359.
PostgreSQL decides not to use the index for the WHERE condition, but the index that supports the ORDER BY. Note that because of the IN it is not possible to use one index for both the WHERE condition and the ORDER BY – that is only possible for WHERE conditions that use = as comparison operator.
So PostgreSQL has to make a choice, and it probably makes the wrong one: since its statistics tell the optimizer that there are many rows that satisfy the WHERE condition, it decides to read the rows in ORDER BY order and discard the ones that don't match the WHERE condition until it has found 100 result rows. Unfortunately, it seems like the matching rows are not close to the beginning of the table, and PostgreSQL has to scan many rows (Rows Removed by Filter: 17276154).
To make it use an index scan for the WHERE condition, modify the ORDER BY clause so that PostgreSQL cannot use an index for it:
ORDER BY datetime + INTERVAL '0 seconds' DESC
Since there is no use for a multi-column index here, the best index would be
CREATE INDEX ON report (sensor_id);
Upvotes: 1
Reputation: 28346
I think the key difference there will be in the number of rows examined.
Mongod uses the index {"sensor_id" : 1, "datetime" : -1} with a separate scan for each distinct value in the $in array, because of the limit, it can stop after it finds 100 matches for each, total of 1100 keys (you might run explain in mongo to verfiy that number). Each of the 11 lists will be already sorted by datetime, so it then merges the results together, and returns the top 100.
Postgres looks like it is using the index on on report (datetime desc, sensor_id), which I believe implies it is scanning over all values in datetime order, and selecting those with a sensor_id that matches one of the input values. The explain shows it examing rows=1747758 which is a lot more than the 1100 probably examined by mongo.
The second run looks at the same number of rows, but completes a lot faster, suggesting that much of the required data was already in the cache.
Upvotes: 0
Related Questions
- SQL Query after migrate from PostgreSQL to MongoDB
- MongoDB and PostgreSQL thoughts
- How to hande joins in Mongodb?
- Replace PostgreSQL with MongoDB?
- MongoDB with PostgreSQL in One Rails App
- query in mongo db
- Mongo - MySql between like
- Migration from Postgres to MongoDB
- The limitations of MongoDB?
- Building queries in mongodb