pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed7.4 - FK constraint performance
29+ messages / 5 participants
[nested] [flat]
* 7.4 - FK constraint performance
@ 2004-02-12 00:06 ow <oneway_111@yahoo.com>
2004-02-12 01:06 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 03:12 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 29+ messages in thread
From: ow @ 2004-02-12 00:06 UTC (permalink / raw)
To: pgsql-sql
PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96
-- about 10 records
CREATE TABLE my.Small
(
id my.dint NOT NULL,
code my.dvalue NOT NULL,
CONSTRAINT pk_1 PRIMARY KEY (id),
) WITHOUT OIDS;
-- about 80M rows
CREATE TABLE my.Large
(
id my.dlong NOT NULL,
small_id my.dint NOT NULL,
value my.value,
CONSTRAINT pk_2 PRIMARY KEY (id),
CONSTRAINT fk_2 FOREIGN KEY (small_id) REFERENCES my.small (id) ON UPDATE
RESTRICT ON DELETE RESTRICT,
) WITHOUT OIDS;
CREATE INDEX small_fk ON my.Large USING btree (small_id);
---------------------------------------------
The fowllowing queiries run in less than 40 ms.
1) select 1 from Large where small_id = 239
2) SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x
3) delete from Small where id = 239
Feb 11 18:07:36 srv07 postgres[2091]: [91-1] LOG: statement: SELECT 1 FROM
ONLY "my"."Large" x WHERE "small_id" = $1 FOR UPDATE OF x Feb 11 18:10:41 srv07
postgres[2091]: [92-1] LOG: duration: 185273.262 ms
When I try to delete record, it takes > 3 min. Why is it taking so long if
practically the same select query (see (2)) is running very quickly. Anything
that can be done to fix it?
Thanks
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-12 01:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 14:05 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2 siblings, 1 reply; 29+ messages in thread
From: Stephan Szabo @ 2004-02-12 01:06 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
On Wed, 11 Feb 2004, ow wrote:
> PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96
>
> -- about 10 records
> CREATE TABLE my.Small
> (
> id my.dint NOT NULL,
> code my.dvalue NOT NULL,
> CONSTRAINT pk_1 PRIMARY KEY (id),
> ) WITHOUT OIDS;
>
> -- about 80M rows
> CREATE TABLE my.Large
> (
> id my.dlong NOT NULL,
> small_id my.dint NOT NULL,
> value my.value,
> CONSTRAINT pk_2 PRIMARY KEY (id),
> CONSTRAINT fk_2 FOREIGN KEY (small_id) REFERENCES my.small (id) ON UPDATE
> RESTRICT ON DELETE RESTRICT,
> ) WITHOUT OIDS;
>
> CREATE INDEX small_fk ON my.Large USING btree (small_id);
>
> ---------------------------------------------
> The fowllowing queiries run in less than 40 ms.
> 1) select 1 from Large where small_id = 239
> 2) SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x
>
> 3) delete from Small where id = 239
> Feb 11 18:07:36 srv07 postgres[2091]: [91-1] LOG: statement: SELECT 1 FROM
> ONLY "my"."Large" x WHERE "small_id" = $1 FOR UPDATE OF x Feb 11 18:10:41 srv07
>
> postgres[2091]: [92-1] LOG: duration: 185273.262 ms
>
> When I try to delete record, it takes > 3 min. Why is it taking so long if
> practically the same select query (see (2)) is running very quickly. Anything
> that can be done to fix it?
Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
case? As a random question, does increasing the statistics target on
Large.small_id and re-analyzing change its behavior?
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 01:06 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-12 14:05 ` ow <oneway_111@yahoo.com>
2004-02-12 15:37 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 29+ messages in thread
From: ow @ 2004-02-12 14:05 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
> case? As a random question, does increasing the statistics target on
> Large.small_id and re-analyzing change its behavior?
Ran analyze, the result is the same. Here's more info:
1) There's 1 row in "Large" for "small_id" = 239
SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x
Quick query. Explain shows index scan.
2) There are many rows in "Large" for "small_id" = 1
SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 1 FOR UPDATE OF x
Runs for about 3 min. Explain shows table scan.
3) delete from Small where id = 239
Runs for about 3 min. It does appear that table scan is used for FK
verification. But why? Am deleting "id = 239" not "id = 1" and the query in
(1) runs very quickly. Had suspicion that wrong id is passed during FK
verification but FK constraint DOES work.
4) Domain types used in the example above
my.dint = int
my.dlong = int8
my.dvalue = varchar(15)
Thanks
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 01:06 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 14:05 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-12 15:37 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 15:53 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
0 siblings, 1 reply; 29+ messages in thread
From: Stephan Szabo @ 2004-02-12 15:37 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
On Thu, 12 Feb 2004, ow wrote:
>
> --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
> > case? As a random question, does increasing the statistics target on
> > Large.small_id and re-analyzing change its behavior?
>
> Ran analyze, the result is the same. Here's more info:
You also did the alter table to up the statistics target on the column,
right?
> 3) delete from Small where id = 239
> Runs for about 3 min. It does appear that table scan is used for FK
> verification. But why? Am deleting "id = 239" not "id = 1" and the query in
> (1) runs very quickly. Had suspicion that wrong id is passed during FK
> verification but FK constraint DOES work.
It doesn't plan it as id=239 but as id=$1 and then executes it with
$1=239. The plan data gets reused for other id values if it needs the
same fk action again later in the session.
I'd hoped that upping the statistics target and re-analyzing would make it
choose an index scan for the case where it doesn't know what constant is
going to be used. Hmm, what is the estimated cost difference and real
time difference on id=1 between seqscan and index scan (explain analyze
output with and without enable_seqscan=off should show you).
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 01:06 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 14:05 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:37 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-12 15:53 ` ow <oneway_111@yahoo.com>
2004-02-12 16:04 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 29+ messages in thread
From: ow @ 2004-02-12 15:53 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> You also did the alter table to up the statistics target on the column,
> right?
Not really. I did not change the the default stats settings in the
postgresql.conf. Not sure what needs to be changed, can you clarify?
Thanks
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 01:06 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 14:05 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:37 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 15:53 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-12 16:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 29+ messages in thread
From: Stephan Szabo @ 2004-02-12 16:04 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
On Thu, 12 Feb 2004, ow wrote:
> --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > You also did the alter table to up the statistics target on the column,
> > right?
>
> Not really. I did not change the the default stats settings in the
> postgresql.conf. Not sure what needs to be changed, can you clarify?
Basically, run something like:
-- 1000 is just an arbitrary choice, but we can lower it later if this
-- works.
ALTER TABLE my.Large ALTER COLUMN small_id SET STATISTICS 1000;
ANALYZE my.Large;
This increases the target for just the column in question which means it
should have a better idea of the distribution and may make it make a
better guess if you've got a somewhat uneven distribution.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-12 03:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 29+ messages in thread
From: Tom Lane @ 2004-02-12 03:12 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
ow <oneway_111@yahoo.com> writes:
> When I try to delete record, it takes > 3 min. Why is it taking so long if
> practically the same select query (see (2)) is running very quickly. Anything
> that can be done to fix it?
What are those column datatypes?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-12 15:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2004-02-12 15:54 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
ow <oneway_111@yahoo.com> writes:
> When I try to delete record, it takes > 3 min.
I think it must be using a seqscan for the foreign key check query.
Could you try this and show the results?
prepare foo(my.dint) as
SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x;
explain analyze execute foo(239);
When I try it I see an indexscan plan, but maybe there's some aspect of
your setup that's causing problems.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-12 21:49 ` ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: ow @ 2004-02-12 21:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ow <oneway_111@yahoo.com> writes:
> > When I try to delete record, it takes > 3 min.
>
> I think it must be using a seqscan for the foreign key check query.
> Could you try this and show the results?
1) SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = 201 FOR UPDATE OF x;
QUERY PLAN
Index Scan using small_fk on large x (cost=0.00..6.01 rows=1 width=6) (actual
time=0.251..0.251 rows=0 loops=1)
Index Cond: ((small_id)::integer = 201)
Total runtime: 0.338 ms
2) prepare foo(my.dint) as
SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x;
explain analyze execute foo(201);
QUERY PLAN
Seq Scan on large x (cost=0.00..1787052.30 rows=7893843 width=6) (actual
time=210566.301..210566.301 rows=0 loops=1)
Filter: ((small_id)::integer = ($1)::integer)
Total runtime: 210566.411 ms
Thanks
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-13 00:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2004-02-13 00:00 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
ow <oneway_111@yahoo.com> writes:
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think it must be using a seqscan for the foreign key check query.
> 2) prepare foo(my.dint) as
> SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x;
> explain analyze execute foo(201);
> QUERY PLAN
> Seq Scan on large x (cost=0.00..1787052.30 rows=7893843 width=6) (actual
> time=210566.301..210566.301 rows=0 loops=1)
> Filter: ((small_id)::integer = ($1)::integer)
> Total runtime: 210566.411 ms
Well, there's the smoking gun all right. Why does it think there are
going to be 7893843 matching rows!? Could we see the pg_stats row for
the large.small_id column?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 00:25 ` ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: ow @ 2004-02-13 00:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, there's the smoking gun all right. Why does it think there are
> going to be 7893843 matching rows!? Could we see the pg_stats row for
> the large.small_id column?
>
> regards, tom lane
schemaname tablename attname null_frac avg_width n_distinct most_common_vals
most_common_freqs histogram_bounds correlation
my large small_id 0 4 10 {7,3,5,1,4,2,8,10,6,9}
{0.108667,0.105,0.104333,0.101333,0.100667,0.0983333,0.0983333,0.0983333,0.094,0.091}
0.0597573
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-13 00:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2004-02-13 00:42 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
ow <oneway_111@yahoo.com> writes:
> schemaname tablename attname null_frac avg_width n_distinct most_common_vals
> most_common_freqs histogram_bounds correlation
> my large small_id 0 4 10 {7,3,5,1,4,2,8,10,6,9}
> {0.108667,0.105,0.104333,0.101333,0.100667,0.0983333,0.0983333,0.0983333,0.094,0.091}
> 0.0597573
According to this entry, your small_id column only contains the ten
values 1..10, roughly evenly distributed. So why are you probing for
239??
The planner is certainly going to estimate a probe for an unspecified
value as retrieving 10% of the table, and under that assumption it's
quite right to use a seqscan.
If this estimate is not right, perhaps you could give us a more accurate
view of the column statistics?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 01:03 ` ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: ow @ 2004-02-13 01:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> According to this entry, your small_id column only contains the ten
> values 1..10, roughly evenly distributed. So why are you probing for
> 239??
Let's say we have City (small) and Person (large) tables. A new city was added
(mistakenly) with id=239, it does not have any "persons" assigned yet. Hence,
we want to remove the wrong "city" record.
In any case, one can't remove record from "small" unless there are NO records
in "large", RI will not allow it. The initial problem was that I tried to
delete a record from "small" and it was taking about 3 min to do that.
> The planner is certainly going to estimate a probe for an unspecified
> value as retrieving 10% of the table, and under that assumption it's
> quite right to use a seqscan.
Sounds pretty bad for my case. Any way to avoid the 10% scan?
Thanks
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-13 01:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:22 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2004-02-13 01:10 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
ow <oneway_111@yahoo.com> writes:
> Sounds pretty bad for my case. Any way to avoid the 10% scan?
Can't see how we optimize your case without pessimizing more-common cases.
Sorry.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 01:22 ` ow <oneway_111@yahoo.com>
2004-02-13 04:49 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 29+ messages in thread
From: ow @ 2004-02-13 01:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Can't see how we optimize your case without pessimizing more-common cases.
My case appears to be pretty common, i.e. 1 small and 1 large table with RI
constraint between them. In order to delete a record from the small table, the
large table must not have records that are dependent on the deleted row.
I think other RDBMSs simply use preset value instead of partial table scan when
there's not enough stat info. Might be a better way.
Thanks
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:22 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
@ 2004-02-13 04:49 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 29+ messages in thread
From: Tom Lane @ 2004-02-13 04:49 UTC (permalink / raw)
To: ow <oneway_111@yahoo.com>; +Cc: pgsql-sql
ow <oneway_111@yahoo.com> writes:
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Can't see how we optimize your case without pessimizing more-common cases.
> I think other RDBMSs simply use preset value instead of partial table
> scan when there's not enough stat info. Might be a better way.
The problem here cannot be described as "not enough stat info". The
available stats are complete and they point very strongly to the
conclusion that searches in the large table should be seqscans.
To do otherwise would be folly in general, even if it happens to
be the correct thing in this particular example.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 02:53 ` Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 29+ messages in thread
From: Rod Taylor @ 2004-02-13 02:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: ow <oneway_111@yahoo.com>; pgsql-sql
On Thu, 2004-02-12 at 20:10, Tom Lane wrote:
> ow <oneway_111@yahoo.com> writes:
> > Sounds pretty bad for my case. Any way to avoid the 10% scan?
>
> Can't see how we optimize your case without pessimizing more-common cases.
> Sorry.
Statistics say there are 10 values. Statistics list the 10 most common
values (all of them). Given this, would it not be reasonable to assume
that 239 is a recent addition (if there at all) to the table and not
very common?
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
@ 2004-02-13 04:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 07:22 ` Re: 7.4 - FK constraint performance Richard Huxton <dev@archonet.com>
2004-02-13 13:57 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 3 replies; 29+ messages in thread
From: Tom Lane @ 2004-02-13 04:25 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: ow <oneway_111@yahoo.com>; pgsql-sql
Rod Taylor <rbt@rbt.ca> writes:
> Statistics say there are 10 values. Statistics list the 10 most common
> values (all of them). Given this, would it not be reasonable to assume
> that 239 is a recent addition (if there at all) to the table and not
> very common?
We don't know that it's 239 when we make the plan. In order to know
that, we'd have to abandon caching of RI check query plans and re-plan
for each row. That strikes me as inevitably a losing proposition.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 07:22 ` Richard Huxton <dev@archonet.com>
2004-02-13 13:57 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2 siblings, 1 reply; 29+ messages in thread
From: Richard Huxton @ 2004-02-13 07:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Rod Taylor <rbt@rbt.ca>; +Cc: ow <oneway_111@yahoo.com>; pgsql-sql
On Friday 13 February 2004 04:25, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Statistics say there are 10 values. Statistics list the 10 most common
> > values (all of them). Given this, would it not be reasonable to assume
> > that 239 is a recent addition (if there at all) to the table and not
> > very common?
>
> We don't know that it's 239 when we make the plan. In order to know
> that, we'd have to abandon caching of RI check query plans and re-plan
> for each row. That strikes me as inevitably a losing proposition.
In this precise example, could you not:
1. Check index for value
2. If found, seq-scan
Of course that's only going to be a sensible thing to do if you're expecting
one of two results:
1. Value not there
2. Lengthy seq-scan if it is there
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 07:22 ` Re: 7.4 - FK constraint performance Richard Huxton <dev@archonet.com>
@ 2004-02-13 13:57 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 29+ messages in thread
From: Rod Taylor @ 2004-02-13 13:57 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; ow <oneway_111@yahoo.com>; pgsql-sql
> In this precise example, could you not:
> 1. Check index for value
> 2. If found, seq-scan
>
> Of course that's only going to be a sensible thing to do if you're expecting
> one of two results:
> 1. Value not there
> 2. Lengthy seq-scan if it is there
Most of the queries are going to be for the other values (in which case
you've wasted an index scan) which is minor, but in the event there is a
single 239 you're still taking a big hit.
That is an awful lot of work to handle the non-existant case only.
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 13:57 ` Rod Taylor <rbt@rbt.ca>
2 siblings, 0 replies; 29+ messages in thread
From: Rod Taylor @ 2004-02-13 13:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: ow <oneway_111@yahoo.com>; pgsql-sql
On Thu, 2004-02-12 at 23:25, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Statistics say there are 10 values. Statistics list the 10 most common
> > values (all of them). Given this, would it not be reasonable to assume
> > that 239 is a recent addition (if there at all) to the table and not
> > very common?
>
> We don't know that it's 239 when we make the plan. In order to know
> that, we'd have to abandon caching of RI check query plans and re-plan
> for each row. That strikes me as inevitably a losing proposition.
Right, sorry, I forgot this was out of a fk trigger.
--
Rod Taylor <rbt [at] rbt [dot] ca>
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 15:02 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 29+ messages in thread
From: Stephan Szabo @ 2004-02-13 15:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rod Taylor <rbt@rbt.ca>; ow <oneway_111@yahoo.com>; pgsql-sql
On Thu, 12 Feb 2004, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Statistics say there are 10 values. Statistics list the 10 most common
> > values (all of them). Given this, would it not be reasonable to assume
> > that 239 is a recent addition (if there at all) to the table and not
> > very common?
>
> We don't know that it's 239 when we make the plan. In order to know
> that, we'd have to abandon caching of RI check query plans and re-plan
> for each row. That strikes me as inevitably a losing proposition.
One thing is that IIRC we're going to ask for only one row when we do the
SPI_execp_current. However, unless I misremember, the behavior of for
update and limit means that saying limit 1 is potentially unsafe (if you
block on a row that goes away). Is there anyway for us to let the planner
know this?
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-13 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:49 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2004-02-13 15:27 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Rod Taylor <rbt@rbt.ca>; ow <oneway_111@yahoo.com>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> One thing is that IIRC we're going to ask for only one row when we do the
> SPI_execp_current. However, unless I misremember, the behavior of for
> update and limit means that saying limit 1 is potentially unsafe (if you
> block on a row that goes away). Is there anyway for us to let the planner
> know this?
I was looking at that last night. It seems like we could add a LIMIT at
least in some contexts. In the case at hand, we're just going to error
out immediately if we find a matching row, and so there's no need for
FOR UPDATE, is there?
However, I'm not sure it would help the OP anyway. With the stats he
had, the planner would still take a seqscan, because it's going to
expect that it can find a match by probing the first ten or so rows of
the first page. With anything close to the normal cost parameters,
that's going to look more expensive than an index probe. Possibly if
the table had a few more values it would work.
But in general it would be a good idea if the planner knew that plan
evaluation would stop after the first row. We could look at passing
that info down out-of-band instead of using LIMIT. There's already
support for this to allow EXISTS() subqueries to be planned properly;
see the tuple_fraction stuff in planner.c. We just can't get at it
via SPI ...
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 15:49 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:56 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Stephan Szabo @ 2004-02-13 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rod Taylor <rbt@rbt.ca>; ow <oneway_111@yahoo.com>; pgsql-sql
On Fri, 13 Feb 2004, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > One thing is that IIRC we're going to ask for only one row when we do the
> > SPI_execp_current. However, unless I misremember, the behavior of for
> > update and limit means that saying limit 1 is potentially unsafe (if you
> > block on a row that goes away). Is there anyway for us to let the planner
> > know this?
>
> I was looking at that last night. It seems like we could add a LIMIT at
> least in some contexts. In the case at hand, we're just going to error
> out immediately if we find a matching row, and so there's no need for
> FOR UPDATE, is there?
I think there still is, because a not yet committed transaction could have
deleted them all in which case I think the correct behavior is to wait and
if that transaction commits allow the action and if it rolls back to
error.
Really we'd want a different behavior where we're only blocking in these
cases if all the matching rows are locked by other transactions.
> However, I'm not sure it would help the OP anyway. With the stats he
> had, the planner would still take a seqscan, because it's going to
> expect that it can find a match by probing the first ten or so rows of
> the first page. With anything close to the normal cost parameters,
> that's going to look more expensive than an index probe. Possibly if
> the table had a few more values it would work.
Hmm, that's true. It also doesn't help the real actions (cascade, set *)
since those really do need to get at all the rows, but it probably helps
in a reasonable number of cases.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:49 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-13 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 16:08 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2004-02-13 15:56 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Rod Taylor <rbt@rbt.ca>; ow <oneway_111@yahoo.com>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 13 Feb 2004, Tom Lane wrote:
>> I was looking at that last night. It seems like we could add a LIMIT at
>> least in some contexts. In the case at hand, we're just going to error
>> out immediately if we find a matching row, and so there's no need for
>> FOR UPDATE, is there?
> I think there still is, because a not yet committed transaction could have
> deleted them all in which case I think the correct behavior is to wait and
> if that transaction commits allow the action and if it rolls back to
> error.
Good point. Okay, we can't put in a LIMIT. But we could still hack the
planner to prefer a fast-start plan by passing an out-of-band tuple
fraction, for those RI plans where it's appropriate. That would not
affect correctness.
>> However, I'm not sure it would help the OP anyway. With the stats he
>> had, the planner would still take a seqscan, because it's going to
>> expect that it can find a match by probing the first ten or so rows of
>> the first page. With anything close to the normal cost parameters,
>> that's going to look more expensive than an index probe.
s/more expensive/less expensive/ ... need more caffeine obviously ...
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:49 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:56 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-13 16:08 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-15 16:41 ` Re: [SQL] 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 29+ messages in thread
From: Stephan Szabo @ 2004-02-13 16:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rod Taylor <rbt@rbt.ca>; ow <oneway_111@yahoo.com>; pgsql-sql
On Fri, 13 Feb 2004, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Fri, 13 Feb 2004, Tom Lane wrote:
> >> I was looking at that last night. It seems like we could add a LIMIT at
> >> least in some contexts. In the case at hand, we're just going to error
> >> out immediately if we find a matching row, and so there's no need for
> >> FOR UPDATE, is there?
>
> > I think there still is, because a not yet committed transaction could have
> > deleted them all in which case I think the correct behavior is to wait and
> > if that transaction commits allow the action and if it rolls back to
> > error.
>
> Good point. Okay, we can't put in a LIMIT. But we could still hack the
> planner to prefer a fast-start plan by passing an out-of-band tuple
> fraction, for those RI plans where it's appropriate. That would not
> affect correctness.
Right, I can try to look through the stuff you pointed at in the previous
message over the weekend.
> >> However, I'm not sure it would help the OP anyway. With the stats he
> >> had, the planner would still take a seqscan, because it's going to
> >> expect that it can find a match by probing the first ten or so rows of
> >> the first page. With anything close to the normal cost parameters,
> >> that's going to look more expensive than an index probe.
>
> s/more expensive/less expensive/ ... need more caffeine obviously ...
Me too apparently, since I knew what you were saying and agreed despite
the wording.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: [SQL] 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:49 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:56 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 16:08 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-15 16:41 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-18 16:03 ` Re: [SQL] 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 29+ messages in thread
From: Stephan Szabo @ 2004-02-15 16:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Fri, 13 Feb 2004, Stephan Szabo wrote:
>
> On Fri, 13 Feb 2004, Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > > On Fri, 13 Feb 2004, Tom Lane wrote:
> > >> I was looking at that last night. It seems like we could add a LIMIT at
> > >> least in some contexts. In the case at hand, we're just going to error
> > >> out immediately if we find a matching row, and so there's no need for
> > >> FOR UPDATE, is there?
> >
> > > I think there still is, because a not yet committed transaction could have
> > > deleted them all in which case I think the correct behavior is to wait and
> > > if that transaction commits allow the action and if it rolls back to
> > > error.
> >
> > Good point. Okay, we can't put in a LIMIT. But we could still hack the
> > planner to prefer a fast-start plan by passing an out-of-band tuple
> > fraction, for those RI plans where it's appropriate. That would not
> > affect correctness.
>
> Right, I can try to look through the stuff you pointed at in the previous
> message over the weekend.
It looks to me that we could make this available to SPI fairly simply by
taking the current version of the following four routines: planner,
pg_plan_query, _SPI_execute and SPI_prepare, renaming them and giving them
a planning tuple fraction as a parameter, change references to the other
routines to the new names and then making four new functions with the
current names that call the renamed versions. In all the cases other than
planner I think we can have the new version pass 0.0 and in the case of
planner either 0.1 or 0.0 based on the isCursor parameter.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: [SQL] 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:49 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:56 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 16:08 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-15 16:41 ` Re: [SQL] 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-18 16:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-18 17:27 ` Re: [SQL] 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Stephan Szabo @ 2004-02-18 16:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Sun, 15 Feb 2004, Stephan Szabo wrote:
> On Fri, 13 Feb 2004, Stephan Szabo wrote:
>
> >
> > On Fri, 13 Feb 2004, Tom Lane wrote:
> >
> > > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > > > On Fri, 13 Feb 2004, Tom Lane wrote:
> > > >> I was looking at that last night. It seems like we could add a LIMIT at
> > > >> least in some contexts. In the case at hand, we're just going to error
> > > >> out immediately if we find a matching row, and so there's no need for
> > > >> FOR UPDATE, is there?
> > >
> > > > I think there still is, because a not yet committed transaction could have
> > > > deleted them all in which case I think the correct behavior is to wait and
> > > > if that transaction commits allow the action and if it rolls back to
> > > > error.
> > >
> > > Good point. Okay, we can't put in a LIMIT. But we could still hack the
> > > planner to prefer a fast-start plan by passing an out-of-band tuple
> > > fraction, for those RI plans where it's appropriate. That would not
> > > affect correctness.
> >
> > Right, I can try to look through the stuff you pointed at in the previous
> > message over the weekend.
>
> It looks to me that we could make this available to SPI fairly simply by
> taking the current version of the following four routines: planner,
> pg_plan_query, _SPI_execute and SPI_prepare, renaming them and giving them
> a planning tuple fraction as a parameter, change references to the other
> routines to the new names and then making four new functions with the
> current names that call the renamed versions. In all the cases other than
> planner I think we can have the new version pass 0.0 and in the case of
> planner either 0.1 or 0.0 based on the isCursor parameter.
I did this, and changed the foreign keys to use it, but I haven't managed
to build a fk case where I could actually detect a change in the plan
chosen. Since the queries are only a simple scan on the one table I'm
wondering if it's basically just modifying both costs by the same value
which means there's no real effect at all.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: [SQL] 7.4 - FK constraint performance
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 15:54 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` Re: 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Re: 7.4 - FK constraint performance Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:02 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:49 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:56 ` Re: 7.4 - FK constraint performance Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 16:08 ` Re: 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-15 16:41 ` Re: [SQL] 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-18 16:03 ` Re: [SQL] 7.4 - FK constraint performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-18 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 29+ messages in thread
From: Tom Lane @ 2004-02-18 17:27 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-hackers@postgresql.org
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I did this, and changed the foreign keys to use it, but I haven't managed
> to build a fk case where I could actually detect a change in the plan
> chosen. Since the queries are only a simple scan on the one table I'm
> wondering if it's basically just modifying both costs by the same value
> which means there's no real effect at all.
After fooling with this, I think you are right. The planner is modeling
both cases as a linear slope from zero to estimated-total-cost, and
since the total number of tuples to be returned is the same, taking a
percentage won't change the outcome.
The only way we could improve the situation would be to somehow instruct
the planner that even though we don't know the parameter value to be
used when we are planning, it should expect that that value is *not* in
the table, rather than expecting that it has a distribution similar to
what is in the table. Seems pretty messy.
I have occasionally speculated about postponing planning of
parameterized queries until they are first executed, and then using the
actual parameter values supplied in that first execution for purposes of
estimating costs and selectivity. That would work pretty nicely in this
particular case, but in the real world I think it'd be mighty dangerous;
you could end up optimizing for an outlier case that isn't
representative of the queries you'll see later.
Another interesting line of thought is to let the user supply
representative values to be used for planning purposes. In PREPARE,
you could imagine saying something like
PREPARE myquery (int = 42, text = 'http://www...';) AS
SELECT ... where url like $2 ...
and then using the sample value 'http://www...'; for purposes of
estimating the LIKE result. Then it'd be on the user's head to pick
good representatives --- but he could make sure that they really were
representative, and not have to worry about luck of the draw from the
first live query.
I'm not sure if we could use such a feature automatically to bias FK
queries in the right direction, but it's something to think about.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2004-02-18 17:27 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-12 00:06 7.4 - FK constraint performance ow <oneway_111@yahoo.com>
2004-02-12 01:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 14:05 ` ow <oneway_111@yahoo.com>
2004-02-12 15:37 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 15:53 ` ow <oneway_111@yahoo.com>
2004-02-12 16:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-12 03:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 15:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-12 21:49 ` ow <oneway_111@yahoo.com>
2004-02-13 00:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 00:25 ` ow <oneway_111@yahoo.com>
2004-02-13 00:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:03 ` ow <oneway_111@yahoo.com>
2004-02-13 01:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 01:22 ` ow <oneway_111@yahoo.com>
2004-02-13 04:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:53 ` Rod Taylor <rbt@rbt.ca>
2004-02-13 04:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 07:22 ` Richard Huxton <dev@archonet.com>
2004-02-13 13:57 ` Rod Taylor <rbt@rbt.ca>
2004-02-13 13:57 ` Rod Taylor <rbt@rbt.ca>
2004-02-13 15:02 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 15:49 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-13 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 16:08 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-15 16:41 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-18 16:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-18 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox