Sql, query optimization and internal join?

Question

I am trying to optimize my query, it has an internal join and merges.

Connection table, a simple table with one integer field, I added a unique key.

For my where clause, I created a key for three fields.

But when I look at the plan, he still talks about it using a table scan.

Where am I going wrong?

Here is my request

select date(a.startdate, '+'||(bn*a.interval)||' '||a.intervaltype) as due from billsndeposits a inner join util_nums b on date(a.startdate, '+'||(bn*a.interval)||' '||a.intervaltype) <= coalesce(a.enddate, date('2013-02-26')) where not (intervaltype = 'once' or interval = 0) and factid = 1 order by due, pid; 

Answer 1

I think I understand what you are trying to achieve. But such a union is a recipe for slow work. Even if you delete date calculations and coalescence (i.e., Compare one date with another), it will still be slow (compared to whole joins) even with the index. And since you create new dates on the fly, you cannot index them.

I suggest creating a temporary table with two columns (1) pid (or any other identifier that you use in billsndeposits ) and (2) recurrence_dt

populate a new table using this query:

 INSERT INTO TEMP SELECT PID, date(a.startdate, '+'||(bn*a.interval)||' '||a.intervaltype) FROM billsndeposits a, util_numbs b; 

Then create an index in the columns recurrence_dt and runstats. Your select statement may now look like this:

 SELECT recurrence_dt FROM temp t, billsndeposits a WHERE t.pid = a.pid AND recurrence_dt <= coalesce(a.enddate, date('2013-02-26')) 

you can add exp_ts to this new table and expire temporary data later.

I know that this adds more work to your original request, but it is a guaranteed increase in performance and should fit naturally in a script that runs often.

Hi,

Edit

Another thing I would do is to do enddate default value = date ('2013-02-26'), unless it affects another code and / or makes sense for the business. Thus, you do not need to work with coalesce.

Answer 2

Most likely, your JOIN expression cannot use any index and is calculated by performing a NATURAL scan and calculating date(a.startdate, '+'||(bn*a.interval)||' '||a.intervaltype) for each row.

BTW: This is a really strange join condition. I suggest you find the best way to join billsndeposits to util_nums (if you really need to).