Skip to content

Instantly share code, notes, and snippets.

@risent

risent/query explain

Created May 21, 2016 03:30
Show Gist options
  • Save risent/f64d03fc4de7a88715921be79613ecbf to your computer and use it in GitHub Desktop.
Save risent/f64d03fc4de7a88715921be79613ecbf to your computer and use it in GitHub Desktop.
Download ZIP
limit parameter effect PostgreSQL query plan
Raw
query explain
bazaar4_13=# explain SELECT "store_snpool"."id", "store_snpool"."event_id", "store_snpool"."code" FROM "store_snpool" WHERE "store_snpool"."event_id" = 9967 ORDER
BY "store_snpool"."id" ASC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.43..61.15 rows=1 width=12)
-> Index Scan using store_snpool_pkey on store_snpool (cost=0.43..118529.70 rows=1952 width=12)
Filter: (event_id = 9967)
(3 rows)
bazaar4_13=# explain SELECT "store_snpool"."id", "store_snpool"."event_id", "store_snpool"."code" FROM "store_snpool" WHERE "store_snpool"."event_id" = 9967 ORDER
BY "store_snpool"."id" ASC LIMIT 2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.43..121.87 rows=2 width=12)
-> Index Scan using store_snpool_pkey on store_snpool (cost=0.43..118529.70 rows=1952 width=12)
Filter: (event_id = 9967)
(3 rows)
bazaar4_13=# explain SELECT "store_snpool"."id", "store_snpool"."event_id", "store_snpool"."code" FROM "store_snpool" WHERE "store_snpool"."event_id" = 9967 ORDER
BY "store_snpool"."id" ASC LIMIT 3;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Limit (cost=158.99..159.00 rows=3 width=12)
-> Sort (cost=158.99..163.87 rows=1952 width=12)
Sort Key: id
-> Index Scan using store_snpool_event_id on store_snpool (cost=0.43..133.76 rows=1952 width=12)
Index Cond: (event_id = 9967)
(5 rows)
Raw
table schema
bazaar4_13=# \d store_snpool
Table "public.store_snpool"
Column | Type | Modifiers
----------+---------+-----------------------------------------------------------
id | integer | not null default nextval('store_snpool_id_seq'::regclass)
event_id | integer | not null
code | integer | not null default 0
Indexes:
"store_snpool_pkey" PRIMARY KEY, btree (id)
"store_snpool_event_id" btree (event_id)
Check constraints:
"store_snpool_code_check" CHECK (code >= 0)
Foreign-key constraints:
"event_id_refs_id_455fd5f3243b48eb" FOREIGN KEY (event_id) REFERENCES store_event(id) DEFERRABLE INITIALLY DEFERRED
@risent
Copy link
Author

risent commented Aug 30, 2016 

bazaar4_13> explain analyze  SELECT "store_snpool"."id", "store_snpool"."event_id", "store_snpool"."code" FROM "store_snpool" WHERE "store_snpool"."event_id" = 9967 ORDER
...........  BY "store_snpool"."id" ASC LIMIT 2;
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                            |
|-------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=115.64..115.65 rows=2 width=12) (actual time=2.549..2.552 rows=2 loops=1)                                                                |
|   ->  Sort  (cost=115.64..119.44 rows=1521 width=12) (actual time=2.547..2.548 rows=2 loops=1)                                                        |
|         Sort Key: id                                                                                                                                  |
|         Sort Method: top-N heapsort  Memory: 25kB                                                                                                     |
|         ->  Index Scan using store_snpool_event_id on store_snpool  (cost=0.43..100.43 rows=1521 width=12) (actual time=2.522..2.531 rows=10 loops=1) |
|               Index Cond: (event_id = 9967)                                                                                                           |
| Planning time: 0.072 ms                                                                                                                               |
| Execution time: 2.575 ms                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.005s
bazaar4_13> explain analyze  SELECT "store_snpool"."id", "store_snpool"."event_id", "store_snpool"."code" FROM "store_snpool" WHERE "store_snpool"."event_id" = 9967 ORDER
...........  BY "store_snpool"."id" ASC LIMIT 3;
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                            |
|-------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=120.09..120.10 rows=3 width=12) (actual time=0.046..0.052 rows=3 loops=1)                                                                |
|   ->  Sort  (cost=120.09..123.89 rows=1521 width=12) (actual time=0.045..0.047 rows=3 loops=1)                                                        |
|         Sort Key: id                                                                                                                                  |
|         Sort Method: top-N heapsort  Memory: 25kB                                                                                                     |
|         ->  Index Scan using store_snpool_event_id on store_snpool  (cost=0.43..100.43 rows=1521 width=12) (actual time=0.017..0.024 rows=10 loops=1) |
|               Index Cond: (event_id = 9967)                                                                                                           |
| Planning time: 0.073 ms                                                                                                                               |
| Execution time: 0.074 ms                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.005s
bazaar4_13> explain analyze  SELECT "store_snpool"."id", "store_snpool"."event_id", "store_snpool"."code" FROM "store_snpool" WHERE "store_snpool"."event_id" = 9967 ORDER
...........  BY "store_snpool"."id" ASC LIMIT 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                          |
|-----------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=0.43..83.89 rows=1 width=12) (actual time=2192.479..2192.480 rows=1 loops=1)                                                           |
|   ->  Index Scan using store_snpool_pkey on store_snpool  (cost=0.43..126950.39 rows=1521 width=12) (actual time=2192.476..2192.476 rows=1 loops=1) |
|         Filter: (event_id = 9967)                                                                                                                   |
|         Rows Removed by Filter: 3498685                                                                                                             |
| Planning time: 0.066 ms                                                                                                                             |
| Execution time: 2192.503 ms                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 2.197s (2 seconds)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment