Home > mailing lists

Re: Partitioned tables and SELECT ... ORDER BY ... LIMIT - Mailing list pgsql-performance

From	Дмитрий Шалашов
Subject	Re: Partitioned tables and SELECT ... ORDER BY ... LIMIT
Date	October 16, 2014 16:33:37
Msg-id	CAKPeCUEpdU_dUs3CDMRK=w2qT1udPwyf8tZKx2Xw3q6Dk17cuw@mail.gmail.com Whole thread Raw
In response to	Re: Partitioned tables and SELECT ... ORDER BY ... LIMIT (François Beausoleil <francois@teksol.info>)
List	pgsql-performance

Tree view

Hi!

So this is not obviously normal, I guess?)

My version is 9.2.9, constraint_exclusion set to 'partition'.

# \d user_feed_master

Table "public.user_feed_master"

Column | Type | Modifiers

------------+-----------------------------+---------------------------------------------------------------

id | bigint | not null default nextval('user_feed_master_id_seq'::regclass)

user_id | integer | not null

type | smallint | not null

added | timestamp without time zone | not null

active_id | integer | not null

url_id | integer | not null

channel_id | integer |

updated | timestamp without time zone | default now()

activity | text |

Number of child tables: 11 (Use \d+ to list them.)

# \d user_feed_201406 -- one of partitions

Table "public.user_feed_201406"

Column | Type | Modifiers

------------+-----------------------------+---------------------------------------------------------------

id | bigint | not null default nextval('user_feed_master_id_seq'::regclass)

user_id | integer | not null

type | smallint | not null

added | timestamp without time zone | not null

active_id | integer | not null

url_id | integer | not null

channel_id | integer |

updated | timestamp without time zone | default now()

activity | text |

Indexes:

"user_feed_201406_pkey" PRIMARY KEY, btree (id)

"user_feed_201406_url_id_user_id_idx" btree (url_id, user_id)

"user_feed_201406_user_id_active_id_added_idx" btree (user_id, active_id, added DESC)

"user_feed_201406_user_id_added_idx" btree (user_id, added DESC)

Check constraints:

"user_feed_201406_added_check" CHECK (added >= '2014-06-01'::date AND added < '2014-07-01'::date)

Inherits: user_feed_master

# SELECT count(*) FROM user_feed_201406 WHERE user_id = 83586; count

-------

909

(1 row)

EXPLAIN (ANALYZE,BUFFERS) SELECT url_id FROM user_feed_master WHERE user_id = 83586 AND added <= '2014-06-30 23:59:59.99999' ORDER BY added DESC LIMIT 100;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------

Limit (cost=0.13..397.23 rows=100 width=12) (actual time=107.442..107.706 rows=100 loops=1)

Buffers: shared hit=104 read=18

I/O Timings: read=107.131

-> Result (cost=0.13..19664.74 rows=4952 width=12) (actual time=107.442..107.695 rows=100 loops=1)

Buffers: shared hit=104 read=18

I/O Timings: read=107.131

-> Merge Append (cost=0.13..19664.74 rows=4952 width=12) (actual time=107.440..107.683 rows=100 loops=1)

Sort Key: public.user_feed_master.added

Buffers: shared hit=104 read=18

I/O Timings: read=107.131

-> Sort (cost=0.01..0.02 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=1)

Sort Key: public.user_feed_master.added

Sort Method: quicksort Memory: 25kB

-> Seq Scan on user_feed_master (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)

Filter: ((added <= '2014-06-30 23:59:59.63551'::timestamp without time zone) AND (user_id = 83586))

-> Index Scan using user_feed_201312_user_id_added_idx on user_feed_201312 user_feed_master (cost=0.00..1525.71 row

s=392 width=12) (actual time=15.020..15.020 rows=1 loops=1)