Something in here really smells fishy to me. Those extract's above are
working on values which are from the table.. Why aren't you using these
functions to figure out how to construct the actual dates based on the
values provided by the *user*..?
Because I've only been using PostgreSQL for one week. For the last several years I've been developing with Oracle on mid-sized systems (40 million books, 5 million reservations per year, etc.). And even then, primarily on the user-facing side of the applications.
Looking at your screenshot, I think you need to take those two date
values that the user provides, make them into actual dates (maybe you
need a CASE statement or something similar, that shouldn't be that hard,
So the user selects Dec 22 and Mar 22 for 1900 to 2009 and the system feeds the report a WHERE clause that looks like:
m.taken BETWEEN '22-12-1900'::date AND '22-03-1901'::date and
m.taken BETWEEN '22-12-1901'::date AND '22-03-1902'::date and
m.taken BETWEEN '22-12-1902'::date AND '22-03-1903'::date and ...
m.taken BETWEEN '22-12-1901'::date AND '22-03-1902'::date and
m.taken BETWEEN '22-12-1902'::date AND '22-03-1903'::date and ...
That tightly couples the report query to the code that sets the report engine parameters. One of the parameters would be SQL code in the form of a dynamically crafted WHERE clause. I'd rather keep the SQL code that is used to create the report entirely with the report engine if at all possible.
Also, you're trying to do constraint_exclusion, but have you made sure
that it's turned on? And have you made sure that those constraints are
really the right ones and that they make sense? You're using a bunch of
extract()'s there too, why not just specify a CHECK constraint on the
date ranges which are allowed in the table..?
I don't know what the date ranges are? So I can't partition them by year?
Right now I created 72 child tables by using the category and month. This may have been a bad choice. But at least all the data is in the system now so dissecting or integrating it back in different ways shouldn't take days.
Thanks everyone for all your help, I really appreciate the time you've taken to guide me in the right direction to make the system as fast as it can be.
Dave
