Re: Limit and inherited tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Limit and inherited tables
Date: 2016-01-15 14:58:36
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> writes:
> I noticed that LIMIT clause is not pushed down to inherited tables.

It is when appropriate.

> Consider the following tables:

> create table foo(x integer primary key);
> create table foo1 () inherits(foo);
> create table foo2 () inherits(foo);
> insert into foo1 values (generate_series(0,100000));
> insert into foo2 values (generate_series(0,100000));

This example is lacking indexes on the child tables, which is
why the plan shown is about as good as you're going to get.
The contents of foo1 and foo2 have to be read in entirety in any
case, and sorting them separately is not a win compared to doing
a single sort.

With indexes, you get something like

Limit (cost=0.73..0.78 rows=1 width=4)
-> Merge Append (cost=0.73..9778.76 rows=200003 width=4)
Sort Key: foo.x
-> Index Only Scan using foo_pkey on foo (cost=0.12..8.14 rows=1 width=4)
-> Index Only Scan using foo1_x_idx on foo1 (cost=0.29..3050.31 rows=100001 width=4)
-> Index Only Scan using foo2_x_idx on foo2 (cost=0.29..3050.31 rows=100001 width=4)

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Benedikt Grundmann 2016-01-15 15:12:28 Death by regexp_replace
Previous Message Fabien COELHO 2016-01-15 14:53:59 Re: extend pgbench expressions with functions