Re: FW: Constraint exclusion in partitions

Hi Daniel:

Bill Moran already pointed out some things, I more or less agree with him....

On Sun, May 24, 2015 at 12:16 AM, Daniel Begin <jfd553(at)hotmail(dot)com> wrote:
......
> I use pgadmin_III sql window.

I'll just point from my experience. At work, more than three fourths
of the problems I've had to help my pgadmin using colleagues solve
where due to their usage of pgadmin. Nearly every time they did dome
db administration task, they did it poorly and begun to do it
correctly when using plain postgresql. This may be anecdotal.

> I write the following query (I have changed
> the id to make sure it does not use previous results still in memory)...

That is exactly the opposite of what you should do, unless you are
able to thorougly clean the server caches ( not as easy as it sounds )
between runs. You want warm caches first, to account for the times in
planning, hashing and joining and so. Once you've got that part ready,
you can move on to real queries, busy server, etc for more fine
tuning.

....
> Now I select "explain query" from the menu and I get the following result...
...
> Now, I select "run" and I get one record as a result and the following
> message in history tab...
.....

As I never use pgadmin, I cannot tell you too much, but one thing I
know is NONE of MY users was able to tell me what pgadmin does when
you hit run and/or explain query. OTOH, I know what psql does. And, if
you use EXPLAIN ANALYZE, as suggested by my previous message, you'll
be better of ( as in this case you just get a single query result from
the server and you do not have to care about what your tool of choice
does in the middle ).

....lotta deletions,

> I really need to get much faster results with my queries on this large table
> and partitioning was my last option...

Make sure you are not having an XY-problem. Also, from the type of
queries you've shown us, I do not think partitioning is the way to go.
It will only give you marginal improvements when querying for single
or range of ids ( and small ones if your ranges of ids are somehow
clustered in your tables ).

Francisco Olarte.