The bitmask allows the setting of multiple permissions but the table
definition doesn't have to change (well, so long as the bits fit into a
word!) Finally, this is a message forum - the actual code itself is
template-driven and the bitmask permission structure is ALL OVER the
templates; gettin
Tom Lane wrote:
> Karl Denninger writes:
>
>> The problem appearsa to lie in the "nested loop", and I don't understand
>> why that's happening.
>>
> It looks to me like there are several issues here.
>
> One is the drastic underestimate of the number of rows satisfying the
> permission con
Karl Denninger writes:
> The problem appearsa to lie in the "nested loop", and I don't understand
> why that's happening.
It looks to me like there are several issues here.
One is the drastic underestimate of the number of rows satisfying the
permission condition. That leads the planner to think
Kevin Grittner wrote:
> Karl Denninger wrote:
>
>>-> Index Scan using forum_name on forum
>> (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408
>> rows=63 loops=1)
>> Filter: (((contrib IS NULL) OR (contrib = '
>> '::text) OR (contrib ~~ '%b%':
> increase default stats target, analyze, try again.
This field has only 5 values. I had put values/frequencies in my first post.
Based on the values (see below) - there is no reason for planner to think that
mt_flags&134=0 should return 12200 rows.
select mt_flags, count(*) from staging.tmp_t gro
Hi Scott,
Thank you for reply.
I am using Postgres 8.4.0 (btw - great release --very happy about it) and I got
a different plan after following your advice:
create index t_mtflags_bit on staging.tmp_t ((mt_flags&8));
analyze staging.tmp_t;
explain analyze select count(*) from staging.tmp_t where
2009/8/18 Slava Moudry :
>> increase default stats target, analyze, try again.
> This field has only 5 values. I had put values/frequencies in my first post.
Sorry, kinda missed that. Anyway, there's no way for pg to know which
operation is gonna match. Without an index on it. So my guess is
th
Karl Denninger wrote:
>-> Index Scan using forum_name on forum
> (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408
> rows=63 loops=1)
> Filter: (((contrib IS NULL) OR (contrib = '
> '::text) OR (contrib ~~ '%b%'::text)) AND ((permission & 127) =
2009/8/18 Slava Moudry :
> Hi Scott,
> Thank you for reply.
> I am using Postgres 8.4.0 (btw - great release --very happy about it) and I
> got a different plan after following your advice:
Yeah, you're returning most of the rows, so a seq scan makes sense.
Try indexing / matching on something mo
First query:
ticker=# explain analyze select * from post, forum where forum.name =
post.forum and invisible <> 1 and to_tsvector('english', message) @@
to_tsquery('violence') order by modified desc limit 100;
QUERY
PLAN
Karl Denninger wrote:
> Let's take the following EXPLAIN results:
We could tell a lot more from EXPLAIN ANALYZE results.
The table definitions (with index information) would help, too.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to
Let's take the following EXPLAIN results:
ticker=# explain select * from post, forum where forum.name = post.forum
and invisible <> 1 and to_tsvector('english', message) @@
to_tsquery('violence') order by modified desc limit
100;
Matthew Wakeling writes:
> -> Index Scan using locationbin8000__subjectobjectbin on
> locationbin8000 l1
> (cost=0.00..71635.23 rows=657430 width=20)
> (actual time=0.056..170.857 rows=664588 loops=1)
> Index Cond:
On Tue, 18 Aug 2009, Tom Lane wrote:
I would be more curious in the poster's situation to turn off
enable_seqscan, enable_sort, and/or enable_nestloop see how the index
scan merge join plan runs.
Like this:
QUERY PLAN
Greg Stark writes:
> If my recollection is right the reason we put the materialize above
> the sort node has to do with Simon's deferred final merge pass
> optimization. The materialize was a way to lazily build the final
> merge as we do the merge but still have the ability to rewind.
> I would
On Tue, Aug 18, 2009 at 5:57 PM, Tom Lane wrote:
> Hmmm ... actually, after looking at the code, I notice that we only
> consider adding a Materialize node to buffer an inner input that is a
> Sort node. The idea was suggested by Greg Stark, if memory serves.
> I wonder now if it'd be worthwhile t
I wrote:
> Matthew Wakeling writes:
>> Very clever. Yes, that is what is happening. I'm surprised that the system
>> doesn't buffer the inner side to avoid having to rescan each time, but
>> then I guess you would have problems if the buffer grew larger than
>> memory.
> Well, it does consider
Matthew Wakeling writes:
> Very clever. Yes, that is what is happening. I'm surprised that the system
> doesn't buffer the inner side to avoid having to rescan each time, but
> then I guess you would have problems if the buffer grew larger than
> memory.
Well, it does consider adding a Materia
On Tue, 18 Aug 2009, Tom Lane wrote:
Matthew Wakeling writes:
I'm seeing some interesting behaviour. I'm executing a query where I
perform a merge join between two copies of the same table, completely
symmetrically, and the two sides of the merge are sourced differently.
This is not as surpri
Matthew Wakeling writes:
> I'm seeing some interesting behaviour. I'm executing a query where I
> perform a merge join between two copies of the same table, completely
> symmetrically, and the two sides of the merge are sourced differently.
This is not as surprising as you think. A mergejoin i
Culley Harrelson wrote:
I will definitely look into this. I suspect I need to tune my kernel
settings first though...
No, not much. Sysctl and loader.conf settings are enough.
$ cat /etc/sysctl.conf
kern.ipc.shmmax=268435456
kern.ipc.shmall=65536
shmmax is in bytes, so this is 256 MB - wa
I'm seeing some interesting behaviour. I'm executing a query where I
perform a merge join between two copies of the same table, completely
symmetrically, and the two sides of the merge are sourced differently.
SELECT COUNT(*)
FROM
(SELECT DISTINCT
l1.objectid,
l1.id AS id1
On Tue, 18 Aug 2009 06:25:57 +0200, Russell Smith
wrote:
Kai Behncke wrote:
But I would like to get it in a php-script, like
$timerequest_result=pg_result($timerequest,0);
(well, that does not work).
I wonder: Is there another way to get the time a request needs?
How do you handle this?
On Mon, Aug 17, 2009 at 2:07 PM, Slava Moudry wrote:
> Hi,
>
> I am using int8 field to pack a number of error flags. This is very common
> technique for large tables to pack multiple flags in one integer field.
>
> For most records – the mt_flags field is 0. Here is the statistics (taken
> from pg
24 matches
Mail list logo
