Re: FW: Constraint exclusion in partitions

On Sat, 23 May 2015 18:16:43 -0400
Daniel Begin <jfd553(at)hotmail(dot)com> wrote:

> Hello Bill,
> You wrote that my testing methodology is flawed - I hope you are right!
>
> However, I am a bit confused about your comments. Yes, I did edited the name
> of the tables for clarity but if I miss the point I, I will do it again as I
> am writing without modifying anything. Here is the procedure I follow and
> results...
>
> I use pgadmin_III sql window. I write the following query (I have changed
> the id to make sure it does not use previous results still in memory)...

I didn't realize you were using PGAdmin ... that explains some of it ...
see below:

> Select * from nodes where id=345678912; -- nodes is the real partitioned
> table name
>
> Now I select "explain query" from the menu and I get the following result...
> "Append (cost=0.00..384.08 rows=99 width=66)"
> " -> Seq Scan on nodes (cost=0.00..0.00 rows=1 width=66)"
> " Filter: (id = 345678912)"
> " -> Index Scan using nodes19_idversion_pk on nodes_19 (cost=0.56..384.08
> rows=98 width=66)"
> " Index Cond: (id = 345678912)"
>
> Now, I select "run" and I get one record as a result and the following
> message in history tab...
> -- Executing query:
> Select * from nodes where id=345678912;
> Total query runtime: 62 ms.
> 1 row retrieved.
>
> Now, if I use the same query on the original table using the same procedure,
> here is what I get...
> Select * from old_nodes where id=345678912; -- old_nodes is the real
> original table name
>
> Explain gives me the following
> "Index Scan using nodes_idversion_pk on old_nodes (cost=0.70..4437.15
> rows=1682 width=66)"
> " Index Cond: (id = 345678912)"
>
> Running the query gives me the same record with the following message in
> history tab...
> -- Executing query:
> select * from old_nodes where id=345678912;
> Total query runtime: 62 ms.
> 1 row retrieved.
>
> This time, the history tab shows that both took the same time to run (an
> improvement!?)

If your environment is providing such wildly variant results, then
you need to start running multiple tests instead of assuming that a single
run of a query is indicative of a pattern.

--
Bill Moran