Re: union all vs. left outer join

Stefan Rybacki wrote:

> henrik.sorensen@xxxxxxxxx wrote:
>
> what postgreSQL does is joining B with A then joins this result again with
> C, but it doesn't use indices for this (see the output: Seq Scan on c, Seq
> Scan on b, Seq Scan on a (this could be because of there are no statistics
> and the query is still fast enough))
>
> Afterwards it does a loop over the joined tables and the before
> materialized join of D and E.

so after all, postgreSQL did not get it right either.

>
>>
>> It might be that the actual testcase have too few records to demonstrate
>> this accesspath.
>>
>> Looking at the explain from Mysql, it indeed seem that the two access
>> paths A-B-D-E and A-C-D-E is recognized. Can you see if the matching
>> indexes are used ?
>
> Yes I can. ;)
> A-B-D-E and A-C-D-E are for the second query that uses union all.
> For the first query it does:
> E-A-B-C-D
> and it is using index XEM1,XBA,XCA but isn't using any index of table D
which means mySql also failed ...

I am beginning to think what I am trying to do with the query is not
possible.
Is there any reason why the A-B-C has to be materialized ?

>
> Regards
> Stefan
Henrik
.

Relevant Pages

  • [UNIX] SQL Inject in ProFTPD Login against PostgreSQL Using mod_sql
    ... A SQL Inject exists in <http://proftpd.linux.co.uk/> ProFTPD server using ... the mod_sql module to authenticate against PostgreSQL database server. ... mod_sql_postgres is used to authenticate users doing a query to PostgreSQL ... print STDOUT $line; ...
    (Securiteam)
  • Re: PostgreSQL and Firebird comparison via digg
    ... Do you know if using PostgreSQL a query or connection can have a priority set, so it can run quicker than other queries? ... in a POS system the reporting queries should have lower priority than the generated invoices insert queries. ... I know a lot of people are basing this whole threading thing on when PostgreSQL only ran on windows via cygwin Unix emulation and yes that did suck, but that is a thing of the way way past. ...
    (borland.public.delphi.non-technical)
  • Re: union all vs. left outer join
    ... >>> Tested on postgreSQL 8 ... >> The accesspath should ideally be: use index EM1 to find matching records, ... but it doesn't use indices for this (see the output: Seq Scan ... I guess with large tables and proper statistics ...
    (comp.databases.theory)
  • Re: Query problems with PostgreSQL
    ... postgresql database. ... I have a form where I can enter a search query - for instance a last ... 'public.archived_with_photos' is a view defined on the postgresql ... the exact same query failed with the PHP script. ...
    (comp.lang.php)
  • Query problems with PostgreSQL
    ... postgresql database. ... I have a form where I can enter a search query - for instance a last ... 'public.archived_with_photos' is a view defined on the postgresql ... the exact same query failed with the PHP script. ...
    (comp.lang.php)