On Fri, 14 Nov 2003 11:00:38 -0500, "Nick Fankhauser"
<[EMAIL PROTECTED]> wrote:
>Good question... I've never used clustering in PostgreSQL before, so I'm
>unsure. I presume this is like clustering in Oracle where the table is
>ordered to match the index?
Yes, something like that. With the except
> Does actor_case_assignment contain more columns than just the two ids?
> If yes, do these additional fields account for ca. 70 bytes per tuple?
> If not, try
> VACUUM FULL ANALYSE actor_case_assignment;
actor_case_assignment has its own primary key and a "role" field in addition
to the id
On Wed, 12 Nov 2003 13:27:53 -0500, "Nick Fankhauser"
<[EMAIL PROTECTED]> wrote:
>
>> You might have to resort to brute force, like "set enable_nestloop=false".
> -> Seq Scan on
>actor_case_assignment (cost=0.00..209980.49 rows=8669349 width=34) (actua
> You might have to resort to brute force, like "set enable_nestloop=false".
> Just out of curiosity, what do you get if you do that?
I get a different plan, but similar execution time:
Limit (cost=323437.13..323437.13 rows=1 width=115) (actual
time=170921.89..170922.95 rows=1000 loops=1)
"Nick Fankhauser" <[EMAIL PROTECTED]> writes:
> This indicates to me that 1 isn't too shabby as an estimate if the whole
> name is specified, but I'm not sure how this gets altered in the case of a
> "LIKE"
For a pattern like "SANDERS%", the estimate is basically a range estimate
for this conditio
> It looks like you are running with the default statistics target (10).
> Try boosting it to 100 or even more for this column (see ALTER TABLE
> SET STATISTICS, then re-ANALYZE) and see if the estimate gets better.
Here are the results & a few more clues:
prod1=# alter table actor alter column
"Nick Fankhauser" <[EMAIL PROTECTED]> writes:
>> Nick, can you find out why this row count estimation is so far off?
> It's actually correct:
Sure, the 3501 was the "actual". The estimate was 1 row, which was
pretty far off :-(
> Here are the stats:
It looks like you are running with the defau
> >actor_full_name_uppercase on actor (cost=0.00..6.01 rows=1 width=42)
> ^^
> >(actual time=37.62..677.44 rows=3501 loops=1)
> ^
> Nick, can you find out why this row count estimation is so far off?
^^^
>(actual time=37.62..677.44 rows=3501 loops=1)
^
> Nick, can you find out why this row count estimation is so far off?
It's actually correct:
prod1=# select count(actor_id) from actor where actor_full_name_uppercase
like 'SANDERS%';
count
---
3501
(1
On Wed, 12 Nov 2003 08:34:50 -0500, "Nick Fankhauser"
<[EMAIL PROTECTED]> wrote:
> -> Index Scan using
>actor_full_name_uppercase on actor (cost=0.00..6.01 rows=1 width=42)
^^
>(actual time=37.62..677.
[I originally posted this using the wrong E-Mail account, so a double
posting may occur if the first message gets released by the moderator later-
sorry!]
Hi-
I have a query that I'm trying to speed up. I haven't been able to come up
with any workable ideas for speeding it up, so I'm seeking some
11 matches
Mail list logo
