Re: FW: Constraint exclusion in partitions

A large portion of why you describe below is the exact opposite of my
own testing (unfortunately, I don't have the actual test results any
more because I did the tests for a former employer).

In my tests, single lookups against the same column being used to
partition improved performance in direct proportion to the number of
partitions. I.e. if the tables are partitioned on id, and the lookup
is for id, and the table has 10 partitions, the query is 10x faster
on the partitioned version than the non-partitioned verison.

Queries against indexes not partitioned were slightly slower in my
tests, but nowhere near the degree that you're showing below.

I can't help but think that your testing methodology is flawed, but
since you're not showing us what you actually did, it's difficult to
be sure. See below for some specifics on what I'm concerned that you
might be doing wrong ...

On Sat, 23 May 2015 14:37:25 -0400
Daniel Begin <jfd553@hotmail.com> wrote:

> Following Francisco suggestion, I was able to do some tests earlier this morning when the partitioning process
completedand all the resulting tables analyzed. 
>
> Here is what I got on both the original table and its partitioned counterpart while running the same queries. I
testedthem only for a couple of values but in summary... 
>
> Using a constant id:
> All the queries I tried took longer on the partitioned table! I got similar results for multiple records using IN (id
value1,id value2 ...) 
>
> Using a range of ids:
> Surprisingly again, all the queries I tried took longer on the partitioned table!
>
> Using a list of ids from a select clause:
> More surprisingly, the queries I tried took less time on the partitioned table at least when using the primary key.
Usingan indexed field took so long compared to the old table that I cancelled the execution for the new one! 
>
> Guess what, I will get back to my old fat table unless someone tells me I missed something obvious!
> Daniel
>
> Note: Tables/indexes description, queries and execution plans are below.
>
>
>
>
>
>
> Tables/indexes description ----------------------------------------------------------------------------------
> The original table has 3870130000 records. Primary key/index on each partition queries are
> ALTER TABLE oldtable ADD CONSTRAINT oldtable_idversion_pk PRIMARY KEY (id, version);
> CREATE INDEX oldtable_groupid_idx ON oldtable USING btree (group_id);
>
> The partitioned table has 3870130000 records distributed over 87 partitions. Primary key/index on each partition
queriesare 
> ALTER TABLE newtable_xx ADD CONSTRAINT newtablexx_idversion_pk PRIMARY KEY (id, version);
> CREATE INDEX newtablexx_groupid_idx ON newtable_xx USING btree (group_id);
> Where xx is the partition's number suffix
>
> constant id -------------------------------------------------------------------------------------------------------
> select * from oldtable where id=123456789;
> "Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..4437.15 rows=1682 width=66)"
> "  Index Cond: (id = 123456789::bigint)"
> --Total query runtime: 62 ms. 1 rows retrieved
>
>
> select * from newtable where id=123456789;
> "Append  (cost=0.00..20.19 rows=5 width=66)"
> "  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        Filter: (id = 123456789::bigint)"
> "  ->  Index Scan using newtable72_idversion_pk on newtable_72  (cost=0.56..20.19 rows=4 width=66)"
> "        Index Cond: (id = 123456789::bigint)"
> --Total query runtime: 156 ms. 1 rows retrieved

Notice in these results that you're not showing the command that was executed.
The output is mostly likely from the command
"ANALYZE select * from newtable where id=123456789;" but that's not the command
you claim that you ran. In any event, the analyze output doesn't line up with
the times you claim: i.e. Analyze is showing that the first query should take
about 4437 time units to complete, and the second one should take about 20
time units, yet you claim the second one is slower. The other queries below
exhibit a similar pattern.

Are you sure you're not timing ANALYZE itself instead of the query? Because
timing "ANALYZE select * from newtable where id=123456789;" is not going to
be timing the actual time the query took to run. I would certainly expect the
_planning_ of a query against partitioned tables to take longer than non-
partitioned, but I would also expect the execution time to be the opposite.
Hence my theory that you've accidentally timed the ANALYZE instead of the
actual running of the query. Naturally, the total query time is planning +
execution, and my experience shows that the loss in planning speed is more
than made up for by the gain in execution speed.

Perhaps you should show us the exact output of one of your tests, without
editorializing.

> I got similar results for multiple records...
> select * from oldtable where id IN(10000000,1000000000,2000000000,3000000000);
> "Index Scan using oldtable_idversion_pk on oldtable  (cost=0.71..17739.18 rows=6726 width=66)"
> "  Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> --Total query runtime: 187 ms. 4 rows retrieved
>
> select * from newtable where id IN(10000000,1000000000,2000000000,3000000000);
> "Append  (cost=0.00..933.40 rows=223 width=66)"
> "  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        Filter: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> "  ->  Index Scan using newtable01_idversion_pk on newtable_01  (cost=0.57..622.78 rows=156 width=66)"
> "        Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> ...
> "  ->  Index Scan using newtable85_idversion_pk on newtable_85  (cost=0.57..53.37 rows=9 width=66)"
> "        Index Cond: (id = ANY ('{10000000,1000000000,2000000000,3000000000}'::bigint[]))"
> --Total query runtime: 421 ms. 4 rows retrieved
>
>
> range of ids -------------------------------------------------------------------------------------------------------
> select * from oldtable where id between 1522999949 and 1523000049;
> "Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..383.51 rows=144 width=66)"
> "  Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
> Total query runtime: 47 ms. 53 rows retrieved.
>
> select * from newtable where id between 1522999949 and 1523000049;
> "Append  (cost=0.00..408.16 rows=104 width=66)"
> "  ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        Filter: ((id >= 1522999949) AND (id <= 1523000049))"
> "  ->  Index Scan using newtable51_idversion_pk on newtable_51  (cost=0.56..183.52 rows=46 width=66)"
> "        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
> "  ->  Index Scan using newtable52_idversion_pk on newtable_52  (cost=0.56..224.64 rows=57 width=66)"
> "        Index Cond: ((id >= 1522999949) AND (id <= 1523000049))"
> Total query runtime: 78 ms. 53 rows retrieved.
>
>
> list of ids from a select clause
-------------------------------------------------------------------------------------------------------
> --Subset provides 4 ids similar but not identical to the previous query
> select * from oldtable where id IN (select * from subset);
> "Nested Loop  (cost=37.45..886298.00 rows=2028512050 width=66)"
> "  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
> "  ->  Index Scan using oldtable_idversion_pk on oldtable  (cost=0.70..4414.37 rows=1693 width=66)"
> "        Index Cond: (id = subset.id)"
> Total query runtime: 171 ms. 4 rows retrieved.
>
> select * from newtable where id IN (select * from subset)
> "Nested Loop  (cost=36.75..1407672.76 rows=1935067087 width=66)"
> "  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
> "  ->  Append  (cost=0.00..7020.68 rows=1749 width=66)"
> "        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "              Filter: (subset.id = id)"
> "        ->  Index Scan using newtable01_idversion_pk on newtable_01  (cost=0.56..151.97 rows=39 width=66)"
> "              Index Cond: (id = subset.id)"
> ...
> "        ->  Index Scan using newtable86_idversion_pk on newtable_86  (cost=0.56..12.42 rows=2 width=66)"
> "              Index Cond: (id = subset.id)"
> Total query runtime: 140 ms. 4 rows retrieved.
>
>
> Using an index, not the primary key ------------------------------------------------------------------------------
> --Subset provides 58 group_id pointing to 5978 records in the concerned tables
> select * from oldtable where group_id IN (select * from subset)
> "Nested Loop  (cost=37.33..21575715.89 rows=2028512050 width=66)"
> "  ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "        ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
> "  ->  Index Scan using oldtable_groupid_idx on oldtable  (cost=0.58..107364.99 rows=51340 width=66)"
> "        Index Cond: (group_id = subset.id)"
> Total query runtime: 3986 ms. 5978 rows retrieved.
>
>
> select * from newtable where group_id IN (select * from subset)
> "Hash Join  (cost=41.25..138092255.85 rows=1935067087 width=66)"
> "  Hash Cond: (newtable.group_id = subset.id)"
> "  ->  Append  (cost=0.00..84877869.72 rows=3870134173 width=66)"
> "        ->  Seq Scan on newtable  (cost=0.00..0.00 rows=1 width=66)"
> "        ->  Seq Scan on newtable_01  (cost=0.00..946235.96 rows=46526896 width=66)"
> ...
> "        ->  Seq Scan on newtable_86  (cost=0.00..986527.64 rows=44269664 width=66)"
> "  ->  Hash  (cost=38.75..38.75 rows=200 width=8)"
> "        ->  HashAggregate  (cost=36.75..38.75 rows=200 width=8)"
> "              ->  Seq Scan on subset  (cost=0.00..31.40 rows=2140 width=8)"
> Execution Cancelled after 766702 ms !
>
> I tried the same with "SET enable_seqscan = OFF" and got an index scan of all tables;
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Bill Moran