headers
Tom Lane | 6 Apr 2002 17:45
Picon

Re: [SQL] intersect performance (PG 7.1.3 vs 7.2)

d_nardini <at> btconnect.com writes:
> Following are EXPLAIN dumps from each platform for both intersect and 
> union (both return the correct data on each platform).

Did you ever VACUUM ANALYZE these tables on the 7.1 box?  The statistics
estimates from that machine look suspiciously like default values.

If you did, then maybe I can credit 7.2's improved statistics machinery
with a win ;-).  But I bet the better choice of plan on the 7.2 box is
due simply to having any stats at all.

Can't help you with the RPM dependency issues, sorry.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html
Permalink | Reply | 0 comments |
headers
Frank Joerdens | 7 Apr 2002 16:15
Picon

Re: Rule trouble (looks to me exactly like the example)

On Fri, Apr 05, 2002 at 10:42:18AM -0500, Tom Lane wrote:
> Frank Joerdens <frank <at> joerdens.de> writes:
> >> What's happening is that "new.id" is effectively still NULL at the point
> >> where the rule is processed, so the rule WHERE condition fails.  I'm not
> >> sure why you're bothering with that WHERE condition anyway ... 
> 
> > Without the WHERE condition, all rows are affected by the update, and
> > not just the newly inserted one.
> 
> I was speaking of the WHERE new.id > 0 part.

Oh yes, that's silly indeed. I just put it there to make the query look like
the example.

>  However, given that you
> want to affect only the newly-inserted row, ISTM you'd be a lot better
> off with a trigger instead of a rule.

I'll give that a try!

Thanks, Frank

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Permalink | Reply | 0 comments |
headers
Ian Cass | 8 Apr 2002 14:35
Favicon

JOINS and non use of indexes

Hi,

Hope someone can spot where I'm going wrong here.

I'm transferring a database & reporting scripts across from Oracle & a few
of the SQL statements behave a little differently with regards to indexes.
The one that's got me stumped at the moment is this...

select * from messages, statusinds
WHERE statusinds.gateway_id = messages.gateway_id
AND (messages.client_id = '7' AND messages.user_name in ('U66515'))
limit 5;

It's using the index on the messages table. On Oracle, it would do a
sequential index scan on the messages table & lookup the appropriate entry
in the statusinds table using the index. However, on Postgres, I can't get
it to use the statusinds index - it does a sequential scan through the
entire table each time! As you can imagine, it's taking ages to do this
where it used to take a few seconds on Oracle.

I've tried explicitly specifying the JOIN type & I can't seem to find the
right combination. I've tried doing a simple select on statusinds where
gateway_id = 'xxx' and the explain tells me it's doing index lookups.

Indexes are as follows...

-- Index: statusinds_200204_ix1
CREATE UNIQUE INDEX statusinds_ix1 ON statusinds USING btree (gateway_id,
status, logtime);
-- Index: messages_200204_ix1
(Continue reading)

Permalink | Reply | 7 comments |
headers
Ian Cass | 8 Apr 2002 14:44
Favicon

Re: JOINS and non use of indexes

Of course, the messages index looks like this...

CREATE INDEX messages_200204_ix2 ON messages_200204 USING btree (client_id,
user_name);

Duh!

> -- Index: messages_200204_ix1
> CREATE UNIQUE INDEX messages_200204_ix1 ON messages_200204 USING btree

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org
Permalink | Reply | 0 comments |
headers
Jaume Teixi | 8 Apr 2002 16:28

best method for select within all dimensions of an array

hello

when selecting in a table with a text[]

there's an sql command for looking all dimensions of the array
something like 'where text[*] = 'bla' '

or only should be implemented trought a 'for (i=0; i<maxDimension; i++)
select blo where text[i] = 'bla' '

?

bests from barcelona

jaume teixi

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html
Permalink | Reply | 0 comments |
headers
Christopher Kings-Lynne | 8 Apr 2002 16:33
Picon

Re: JOINS and non use of indexes

I haven't had a good look, but just remember that indexes on a table in
postgres are NOT inherited by its children.  You cannot define a unique
index over a column that is inherited - it will be unique for the table you
define it on only.  Hence, you may not actually have indexes on those
inherited tables, and therefore they cannot be used...

Chris

> Explain plan....
> Limit (cost=0.00..35.06 rows=5 width=620)
> -> Nested Loop (cost=0.00..30986063.51 rows=4418898 width=620)
> -> Append (cost=0.00..441.93 rows=111 width=496)
> -> Seq Scan on messages (cost=0.00..0.00 rows=1 width=496)
> -> Index Scan using messages_200203_ix2 on messages_200203 messages
> (cost=0.00..272.61 rows=68 width=383)
> -> Index Scan using messages_200204_ix2 on messages_200204 messages
> (cost=0.00..169.32 rows=42 width=384)
> -> Append (cost=0.00..180413.11 rows=7996912 width=124)
> -> Seq Scan on statusinds (cost=0.00..0.00 rows=1 width=124)
> -> Seq Scan on statusinds_200203 statusinds (cost=0.00..142835.73
> rows=6292073 width=71)
> -> Seq Scan on statusinds_200204 statusinds (cost=0.00..37577.38
> rows=1704838 width=65)
>
> (tables_YYYYMM are inherited)
>
> --
> Ian Cass
>
>
(Continue reading)

Permalink | Reply | 1 comment |
headers
Ian Cass | 8 Apr 2002 16:41
Favicon

Re: JOINS and non use of indexes

You're right. But I do this....

my $sql = "create table messages_$month () inherits (messages)";
...
my $sql = "create unique index messages_" . $month . "_ix1 on
messages_$month using btree (host, qos_id)";
...
my $sql = "create index messages_" . $month . "_ix2 on messages_$month using
btree (client_id, user_name)";
...

Similar thing for my statusinds tables too.

--
Ian Cass

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl <at> familyhealth.com.au>
To: "Ian Cass" <ian.cass <at> mblox.com>; <pgsql-sql <at> postgresql.org>
Sent: Monday, April 08, 2002 3:33 PM
Subject: Re: [SQL] JOINS and non use of indexes

> I haven't had a good look, but just remember that indexes on a table in
> postgres are NOT inherited by its children.  You cannot define a unique
> index over a column that is inherited - it will be unique for the table
you
> define it on only.  Hence, you may not actually have indexes on those
> inherited tables, and therefore they cannot be used...
>
> Chris
(Continue reading)

Permalink | Reply | 0 comments |
headers
Tom Lane | 8 Apr 2002 17:21
Picon

Re: JOINS and non use of indexes

"Ian Cass" <ian.cass <at> mblox.com> writes:
>> The planner isn't smart about
>> indexscan-based joins for inheritance trees.  This could possibly be
>> improved with some work, but I haven't thought about the details.

> Is there any way I could 'educate' it by using more explicit sql, or do I
> have to abandon the idea of using inheritance altogether?

You'd have to spell out the join for each member of the inheritance
tree:

	SELECT ... FROM outside, inside_1 WHERE ...
	UNION ALL
	SELECT ... FROM outside, inside_2 WHERE ...
	UNION ALL
	SELECT ... FROM outside, inside_3 WHERE ...

which is just about as bad as not using inheritance :-(

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Permalink | Reply | 0 comments |
headers
Ian Cass | 8 Apr 2002 17:12
Favicon

Re: JOINS and non use of indexes

> There's your problem (and don't tell me Oracle gets this right; they
> don't do inheritance, do they?).

No, Oracle doesn't do inheritance.

> The planner isn't smart about
> indexscan-based joins for inheritance trees.  This could possibly be
> improved with some work, but I haven't thought about the details.

Is there any way I could 'educate' it by using more explicit sql, or do I
have to abandon the idea of using inheritance altogether?

--
Ian Cass

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Permalink | Reply | 1 comment |
headers
Tom Lane | 8 Apr 2002 16:46
Picon

Re: JOINS and non use of indexes

"Ian Cass" <ian.cass <at> mblox.com> writes:
> I'm transferring a database & reporting scripts across from Oracle & a few
> of the SQL statements behave a little differently with regards to indexes.
> The one that's got me stumped at the moment is this...

> select * from messages, statusinds
> WHERE statusinds.gateway_id = messages.gateway_id
> AND (messages.client_id = '7' AND messages.user_name in ('U66515'))
> limit 5;

> (tables_YYYYMM are inherited)

There's your problem (and don't tell me Oracle gets this right; they
don't do inheritance, do they?).  The planner isn't smart about
indexscan-based joins for inheritance trees.  This could possibly be
improved with some work, but I haven't thought about the details.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly
Permalink | Reply | 3 comments |

Gmane