Skip site navigation (1) Skip section navigation (2)

Re: Mysql 321 - Mysql 322 - msql

From: John Fieber <jfieber(at)indiana(dot)edu>
To: The Hermit Hacker <scrappy(at)hub(dot)org>
Subject: Re: Mysql 321 - Mysql 322 - msql
Date: 1998-11-28 20:10:54
Message-ID: Pine.BSF.4.05.9811281503330.795-200000@fallout.campusview.indiana.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sat, 28 Nov 1998, The Hermit Hacker wrote:

> On Sat, 28 Nov 1998, John Fieber wrote:
> 
> > In working with the two, I've also found a couple complicated
> > join queries where I just couldn't get the optimizer in
> > PostgreSQL (6.3.2 and 6.4) to do the right thing, resulting in
> > several minutes of processing per query, while mySQL did the same
> > query in the blink of an eye.
> 
> 	You mention v6.4 above, so could you provide us with a way of
> "reproducing" the bug?

Attached is the database scheme from pg_dump (there are a bunch
of extraneous tables in the context of this query). I make no
claims at all about the quality of the database design or the
query, but both Sybase and mySQL execute it very quickly while
PostgreSQL refuses to use the index on the codes table.

$sheet in the query is the "user input" to and is an integer.

(For the curious, this is part of
http://fallout.campusview.indiana.edu/mapfinder.  I can supply
data if needed.)

-john

    	SELECT
	    sheet.sheet_id,
	    sheet.name,
    	    sheet.number,
    	    sheet.note,
    	    cat.call,
    	    cat.series,
    	    cat.main_entry,
    	    sheet.scale,
    	    ca.name as mtype,
    	    cb.name as prod,
    	    cc.name as proj,
    	    cd.name as pm,
    	    ce.name as format,
    	    sheet.coords
    	FROM
	    sheet,
	    cat,
	    codes ca,
	    codes cb,
	    codes cc,
	    codes cd,
	    codes ce
    	WHERE
	    sheet.sheet_id = $sheet 
    	    AND sheet.cat_id = cat.cat_id
	    AND sheet.mtype = ca.code_id
	    AND sheet.prod = cb.code_id
	    AND sheet.proj = cc.code_id
	    AND sheet.pm = cd.code_id
	    AND sheet.format = ce.code_id


Attachment: mf.schema
Description: text/plain (1.6 KB)

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 1998-11-28 20:30:34
Subject: Re: [HACKERS] DROPping tables with SERIALs
Previous:From: The Hermit HackerDate: 1998-11-28 19:54:46
Subject: Re: Mysql 321 - Mysql 322 - msql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group