Skip to content

Instantly share code, notes, and snippets.

@ailabs-software
Created September 14, 2019 21:19
Show Gist options
  • Select an option

  • Save ailabs-software/db6cfb58a5e7629c036687e141572a80 to your computer and use it in GitHub Desktop.

Select an option

Save ailabs-software/db6cfb58a5e7629c036687e141572a80 to your computer and use it in GitHub Desktop.
Shouldn't it be now only joining 95,990 rows to the pubish rows?
EXPLAIN ANALYZE
SELECT maturation_timestamp FROM publish_queue LEFT OUTER JOIN publish ON(publish.id=publish_queue.publish_id) WHERE status_executed=0 AND funded AND syndicated=false ORDER BY NOT maturation_timestamp IS NULL, maturation_timestamp ASC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=193275.62..193275.63 rows=1 width=8) (actual time=4720.099..4720.100 rows=1 loops=1)
-> Sort (cost=193275.62..194582.79 rows=522866 width=8) (actual time=4720.098..4720.098 rows=1 loops=1)
Sort Key: ((publish_queue.maturation_timestamp IS NOT NULL)), publish_queue.maturation_timestamp
Sort Method: top-N heapsort Memory: 25kB
-> Hash Join (cost=90697.10..190661.29 rows=522866 width=8) (actual time=3598.829..4720.037 rows=328 loops=1)
Hash Cond: (publish_queue.publish_id = publish.id)
-> Index Scan using publish_queue_status_executed_idx on publish_queue (cost=0.43..77041.65 rows=562209 width=24) (actual time=0.015..140.540 rows=97692 loops=1)
Index Cond: (status_executed = 0)
-> Hash (cost=68678.56..68678.56 rows=1266648 width=16) (actual time=3598.624..3598.624 rows=1218965 loops=1)
Buckets: 65536 Batches: 4 Memory Usage: 14315kB
-> Seq Scan on publish (cost=0.00..68678.56 rows=1266648 width=16) (actual time=0.005..2242.412 rows=1218965 loops=1)
Filter: (funded AND (NOT syndicated))
Rows Removed by Filter: 89164
Total runtime: 4722.336 ms
(14 rows)
# SELECT COUNT(*) FROM publish_queue WHERE status_executed=0;
count
-------
95990
(1 row)
Shouldn't it be now only joining 95,990 rows to the pubish rows?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment