On Sat, 23 May 2015 18:16:43 -0400 Daniel Begin <jfd553@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
