Re: RV: bad result in a query!! :-(

On Tuesday 15 Oct 2002 12:12 pm, Nigel J. Andrews wrote:
> On Tue, 15 Oct 2002, Jose Antonio Leo wrote:
> > Hi, I execute a complex query I get very slow response: Total runtime:
> > 565528.70 msec

That's 9 minutes - not very good at all.

> > The query is:
[snip]
> > WHERE (((aecoc.cod_ae2)=0) AND ((aecoc.cod_ae3)=0) AND
> > ((aecoc.cod_ae4)=0) AND ((aecoc.cod_ae5)=0) AND
> > ((extract (year from vtdiaaec.fecha))='2002'))

This extract will force a seq-scan. You might find it better to check for
dates: 2002-01-01 to 2002-12-31 which could use an index on the field.

Failing that you could write a function year_part(timestamptz) which returned
the relevant date_part() and create a functional index.

> > And the Explain:

> > -> Merge Join (cost=10821.77..12058.67 rows=1485
> > width=182) (actual time=16453.89..557749.04 rows=75918 loops=1)

Long start-up time on this (if I'm reading this right).

> > -> Sort (cost=10821.77..10821.77 rows=1485
> > width=118) (actual time=16453.64..199329.55 rows=49801240 loops=1)
>
> ^^^^^^^^^^^^^^^^^^^^^^^
> What is this all about, the seqscan only returns 75918 rows?

Yep - very strange. I'm not sure where the 4 million comes from - I can't see
any relationship with the 75918.

Nigel's advice about ENABLE_MERGEJOIN should help, but there's something odd
here. Try a VACUUM ANALYSE VERBOSE on the two tables and see if it says
anything odd perhaps.

--
Richard Huxton