Re: [GENERAL] What limits Postgres performance when the whole database lives in cache?
On Thu, Sep 8, 2016 at 2:35 AM, dandl wrote: > I understand that. What I'm trying to get a handle on is the magnitude of > that cost and how it influences other parts of the product, specifically > for Postgres. If the overhead for perfect durability were (say) 10%, few > people would care about the cost. But Stonebraker puts the figure at 2500%! > His presentation says that a pure relational in-memory store can beat a row > store with disk fully cached in memory by 10x to 25x. [Ditto column stores > beat row stores by 10x for complex queries in non-updatable data.] > VoltDB replication is synchronous in the same cluster/data center, and asynchronous with a remote cluster/data center. As a consequence, if your application needs to survive a data center power failure with zero data loss, then you have to enable VoltDB's synchronous command logging (which by the way is not available in the Community Edition — only in the Enterprise Edition). When Stonebraker says VoltDB's throughput is 10~25x greater, I'd guess this is with no command logging at all, and no periodic snapshotting. So my question is not to challenge the Postgres way. It's simply to ask > whether there are any known figures that would directly support or refute > his claims. Does Postgres really spend 96% of its time in thumb-twiddling > once the entire database resides in memory? > Alas, I've been unable to find any relevant benchmark. I'm not motivated enough to install a PostgreSQL and VoltDB and try it for myself :-)
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Tue, Aug 30, 2016 at 1:10 PM, Nicolas Grilly wrote: > We are developing a multitenant application which is currently based on > MySQL, but we're thinking of migrating to PostgreSQL. > > We rely on clustered indexes to preserve data locality for each tenant. > Primary keys start with the tenant ID. This way, rows belonging to the same > tenant are stored next to each other. Because all requests hit only one > tenant, this is a great performance improvement. > > PostgreSQL doesn't have clustered indexes — I'm aware of the CLUSTER > command but it's a one-time operation — and I'm wondering if this can be a > problem or not. > > Let's say we have a table containing data for 10,000 tenants and 10,000 > rows per tenant, for a total of 100,000,000 rows. Let's say each 8 KB block > contains ~10 rows. Let's way we want to compute the sum of an integer > column for all rows belonging to a given tenant ID. > > In MySQL/InnoDB, rows are stored in the leaf nodes of a B-tree. To compute > the sum, MySQL has to read at least 1,000 blocks (each block containing ~10 > rows). I deliberately neglect the cost of walking the B-tree intermediate > nodes. > > By comparison, PostgreSQL has to read at least 10,000 blocks (each block > containing ~10 rows, but most of the time, only one row will match the > tenant ID, other rows belonging to other tenants). > > A few questions: > > - Am I missing something? > - Am I overestimating the benefit of a clustered index in our case, and > the cost of not having one in PostgreSQL? > - Is there another technical solution to this problem? > After a few days thinking and researching about this, I've come to the following conclusion: 1) Trying to preserve data locality in a multi-tenant application is a legitimate requirement (because it can significantly improve performance). 2) But index organized tables are not the right answer. 3) Partitioning by tenant_id, sorting heap rows by tenant_id, and/or creating covering indexes (for index-only scans) are better solutions. I found at least two examples of well-known companies using PostgreSQL at scale for a multi-tenant application, and taking specific steps to preserve data locality: - Heap shards its data by customer_id (all rows in a logical shard belong to the same customer — except for small customers, but it's easy to make their queries fast anyway) [1]. - Instagram uses pg_reorg (the ancestor of pg_repack) to keep likes created by the same user contiguous on disk [2]. At first, I thought that index organized tables (aka clustered indexes) were the solution, and that missing them in PostgreSQL could be an issue, but I was wrong. They are not the right tool for the job. Index organized tables are a powerful tool for an application that needs very fast lookups and range scans on the primary key. But they also have significant drawbacks [3]: - Lookups on secondary keys are slower (because they need one more indirection). - The index is bigger (because rows are stored directly in the index, instead of a heap). - InnoDB can only use the primary key as clustering key, which is very restrictive (for example, in PostgreSQL, most recently inserted/updated rows are naturally clustered together, independently of the chosen primary key). So, PostgreSQL uses heap organized tables instead of index organized tables, and this is a good thing, at least for the kind of queries my application needs. But, at scale, I still need to find a way to preserve data locality for each tenant. The solutions I've identified are: - Partition by tenant_id as suggested by Thomas Kellerer earlier in this thread. Declarative partitioning will make this easier in a future version of PostgreSQL. It's also possible to "shard" using Citus Data (like Heap or CloudFlare). - Periodically sort rows by tenant_id in the heap, using something like pg_repack, as suggested by Kenneth Marshall and Ben Chobot. - Create covering indexes, which let PostgreSQL do index-only scans (exactly like an index organized table), as suggested by Eduardo Morras and engineers at Heap [4]. It looks like I can move forward with our migration from MySQL to PostgreSQL, without worrying about the lack of clustered indexes, because there are better solutions to keep tenant data contiguous! Thanks for all your comments. Nicolas Grilly [1] https://news.ycombinator.com/item?id=12412216 [2] http://instagram-engineering.tumblr.com/post/40781627982 /handling-growth-with-postgres-5-tips-from [3] https://dzone.com/articles/unreasonable-defaults-primary [4] "Powering Heap", https://www.youtube.com/watch?v=NVl9_6J1G60
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Thu, Sep 1, 2016 at 3:08 PM, Igor Neyman wrote: > Don’t know about plans to implement clustered indexes in PostgreSQL. > It was discussed on the mailing list in the past. I found an interesting thread dated from 2012 about integrating pg_reorg (the ancestor of pg_repack) in PostgreSQL core: https://www.postgresql.org/message-id/flat/CAB7nPqTGmNUFi%2BW6F1iwmf7J-o6sY%2Bxxo6Yb%3DmkUVYT-CG-B5A%40mail.gmail.com There is also an item titled "Automatically maintain clustering on a table" in the TODO list: https://wiki.postgresql.org/wiki/Todo#CLUSTER Not sure if this was mentioned, MS SQL Server has clustered indexes, where > heap row is just stored on the leaf level of the index. > > Oracle also has similar feature: IOT, Index Organized Table. > > > > It seems to me (may be I’m wrong), that in PostgreSQL it should be much > harder to implement clustered index (with the heap row stored in the index > leaf) because of the way how MVCC implemented: multiple row versions are > stored in the table itself (e.g. Oracle for that purpose keeps table > “clean” and stores multiple row versions in UNDO tablespace/segment). > > DB2, like PostgreSQL, stores rows in a heap, and not in the leafs of a Btree. But it's possible to define a "clustering" key for a table. When it is defined, DB2 tries to keep the rows in the heap ordered according to the clustering key. If DB2 can’t find space on the page where the row should go, then it looks a few pages before and after and puts it there, and if it still can’t find space then puts it at the end. There is also a feature called "multidimensional clustering" which is even more sophisticated. There is also a command REORG, which would be the equivalent of a non-blocking CLUSTER in PostgreSQL. I think DB2's approach is interesting because it shows that maintaining spatial coherency is possible with a heap, without having to store rows in a Btree (like InnoDB).
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Thu, Sep 1, 2016 at 12:31 AM, Nicolas Grilly wrote: > In DB2, it seems possible to define a "clustering index" that determines > how rows are physically ordered in the "table space" (the heap). > > The documentation says: "When a table has a clustering index, an INSERT > statement causes DB2 to insert the records as nearly as possible in the > order of their index values." > > It looks like a kind of "continuous CLUSTER/pg_repack". Is there something > similar available or planned for PostgreSQL? > I forgot the links to DB2 documentation about clustering index: http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/intro/src/tpc/db2z_clusteringindexes.html http://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0061098.html
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Tue, Aug 30, 2016 at 8:17 PM, Kenneth Marshall wrote: > We have been using the extension pg_repack to keep a table groomed into > cluster order. With an appropriate FILLFACTOR to keep updates on the same > page, it works well. The issue is that it needs space to rebuild the new > index/table. If you have that, it works well. > In DB2, it seems possible to define a "clustering index" that determines how rows are physically ordered in the "table space" (the heap). The documentation says: "When a table has a clustering index, an INSERT statement causes DB2 to insert the records as nearly as possible in the order of their index values." It looks like a kind of "continuous CLUSTER/pg_repack". Is there something similar available or planned for PostgreSQL?
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Thu, Sep 1, 2016 at 12:05 AM, Ben Chobot wrote: > If what they did 3 years ago is similar to what you are trying to do > today, who cares what they are doing today? (Besides using pg_repack > instead of pg_reorg, of course.) > I'm curious because, in the meantime, Instagram could have stopped doing this because 1) it didn't work as intended or 2) they found a better solution. > For what it's worth, we use pg_repack on a regular basis and it works > exactly as advertised. > Thanks.
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Tue, Aug 30, 2016 at 8:17 PM, Kenneth Marshall wrote: > We have been using the extension pg_repack to keep a table groomed into > cluster order. With an appropriate FILLFACTOR to keep updates on the same > page, it works well. The issue is that it needs space to rebuild the new > index/table. If you have that, it works well. > It looks like Instagram has been using pg_reorg (the ancestor of pg_repack) to keep all likes from the same user contiguous on disk, in order to minimize disk seeks. http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from This is very similar to what I'm trying to achieve. The article is 3 years old. I'd be curious to know if they still do that. Nicolas
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Wed, Aug 31, 2016 at 6:05 PM, Kenneth Marshall wrote: > We just run it via cron. In our case, we run it once a day, but depending > on > your churn, it could be run once a week or more. > Could you provide some numbers: what is the size of the tables or tables that are repacked? how long does it take? is there a performance impact during the repack?
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
Mike Sofen wrote: > For Nicolas’s situation, that would require 10,000 partitions – not very > useful, and each partition would be very small. > This is exactly my conclusion about using partitions in my situation. > In Postgres, as you mentioned, clustering is a “one time” operation but > only in the sense that after you add more rows, you’ll need to re-cluster > the table. Depending on the activity model for that table, that may be > feasible/ok. For example, if you load it via regular batch scripts, then > the clustering could be done after those loads. If you add rows only > rarely but then do lots of updates, then the clustering would work great. > If this is an active real time data table, then clustering would not be > viable. > The application is very interactive and news rows are inserted all the time in my use case. Thanks for your time, Nicolas
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
Eduardo Morras wrote: > Check BRIN indexs, they are "designed for handling very large tables in > which certain columns have some natural correlation with their physical > location within the table", I think they fit your needs. Yes, a BRIN index on the tenant ID would be very useful if the rows in the heap were naturally sorted by the tenant ID, but they are not. They are naturally sorted by their order of insertion, which is completely unrelated. The first step in solving this is to find a way to keep rows belonging to the same tenant close to each other. The second step could be to use a BRIN index.
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Tue, Aug 30, 2016 at 8:17 PM, Kenneth Marshall wrote: > We have been using the extension pg_repack to keep a table groomed into > cluster order. With an appropriate FILLFACTOR to keep updates on the same > page, it works well. The issue is that it needs space to rebuild the new > index/table. If you have that, it works well. > Interesting! Do you run pg_repack on a regular schedule using something like cron, or does it run automatically in the background? Is it compatible with PostgreSQL 9.6?
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Tue, Aug 30, 2016 at 7:26 PM, Vick Khera wrote: > I'll assume you have an index on the tenant ID. In that case, your > queries will be pretty fast. > > On some instances, we have multi-column indexes starting with the > tenant ID, and those are used very effectively as well. > > I never worry about data locality. > Yes, we have an index starting with the tenant ID, and the query uses the index. But I'm still worried about PostgreSQL having to fetch 10 times more pages from the disk than MySQL. If each 8K page contains approximately 10 rows, fetching one page in MySQL will return 10 "useful" rows belonging to the tenant. By comparison, fetching one page in PostgreSQL will probably return only 1 "useful" row belonging to the tenant. In terms of IO, it's a big difference. > Depending on your data distribution, you may want to consider table > partitions based on the tenant id. I personally never bother with > that, but split based on some other key in the data. > You're right. I don't really need a clustered index (like InnoDB). What I need is to store rows belonging to the same tenant close from each other. Partitioning can help with that. But the lack of declarative partitioning makes it cumbersome (I've read this is worked on).
[GENERAL] Clustered index to preserve data locality in a multitenant application?
Hello, We are developing a multitenant application which is currently based on MySQL, but we're thinking of migrating to PostgreSQL. We rely on clustered indexes to preserve data locality for each tenant. Primary keys start with the tenant ID. This way, rows belonging to the same tenant are stored next to each other. Because all requests hit only one tenant, this is a great performance improvement. PostgreSQL doesn't have clustered indexes — I'm aware of the CLUSTER command but it's a one-time operation — and I'm wondering if this can be a problem or not. Let's say we have a table containing data for 10,000 tenants and 10,000 rows per tenant, for a total of 100,000,000 rows. Let's say each 8 KB block contains ~10 rows. Let's way we want to compute the sum of an integer column for all rows belonging to a given tenant ID. In MySQL/InnoDB, rows are stored in the leaf nodes of a B-tree. To compute the sum, MySQL has to read at least 1,000 blocks (each block containing ~10 rows). I deliberately neglect the cost of walking the B-tree intermediate nodes. By comparison, PostgreSQL has to read at least 10,000 blocks (each block containing ~10 rows, but most of the time, only one row will match the tenant ID, other rows belonging to other tenants). A few questions: - Am I missing something? - Am I overestimating the benefit of a clustered index in our case, and the cost of not having one in PostgreSQL? - Is there another technical solution to this problem? Thanks, *Nicolas Grilly* *Garden / Vocation City* - Web Recruitment Software Managing Partner +33 6 03 00 25 34 www.vocationcity.com
[GENERAL] Clustered index to preserve data locality in a multitenant application?
Hello, We are developing a multitenant application which is currently based on MySQL, but we're thinking of migrating to PostgreSQL. We rely on clustered indexes to preserve data locality for each tenant. Primary keys start with the tenant ID. This way, rows belonging to the same tenant are stored next to each other. Because all requests hit only one tenant, this is a great performance improvement. PostgreSQL doesn't have clustered indexes — I'm aware of the CLUSTER command but it's a one-time operation — and I'm wondering if this can be a problem or not. Let's say we have a table containing data for 10,000 tenants and 10,000 rows per tenant, for a total of 100,000,000 rows. Let's say each 8 KB block contains ~10 rows. Let's way we want to compute the sum of an integer column for all rows belonging to a given tenant ID. In MySQL/InnoDB, rows are stored in the leaf nodes of a B-tree. To compute the sum, MySQL has to read at least 1,000 blocks (each block containing ~10 rows). I deliberately neglect the cost of walking the B-tree intermediate nodes. By comparison, PostgreSQL has to read at least 10,000 blocks (each block containing ~10 rows, but most of the time, only one row will match the tenant ID, other rows belonging to other tenants). A few questions: - Am I missing something? - Am I overestimating the benefit of a clustered index in our case, and the cost of not having one in PostgreSQL? - Is there another technical solution to this problem? Thanks, Nicolas Grilly Managing Partner +33 6 03 00 25 34 www.vocationcity.com • Recruitment & application tracking software www.gardentechno.com • Consulting & software engineering
Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
There is some good news coming from Oleg Bartunov and Alexander Korotkov about improving ranking speed: http://wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf It's worth reading their slides to gain a better understanding of PostgreSQL fulltext internals. On Wed, Mar 7, 2012 at 8:05 PM, Nicolas Grilly wrote: > In a previous discussion thread, Oleg suggested that ts_rank is unable to > use GIN indices: > http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php > > This is the only information I have about this. >
Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
In a previous discussion thread, Oleg suggested that ts_rank is unable to use GIN indices: http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php This is the only information I have about this. On Wed, Mar 7, 2012 at 18:59, Andrey Chursin wrote: > Is there any way to sort by ranking, avoiding seq scan? > The only way i see now is to use pg_trgm instead of ts_rank, but we > did not check yet how applicable is it for our purposes. >
Re: [GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)
Reopening a six months old discussion... Is there any plans to improve ts_rank performance by making it able to use GIN/GIST indices? >From re-reading Oleg answers, I understand FTS indices do not contain enough information for ts_rank. Because of that, ts_rank has to read the ts_vector, which is stored in TOAST table, which triggers a random read for each ranked document. Cheers, Nicolas Grilly On Wed, Jul 13, 2011 at 18:55, Nicolas Grilly wrote: > The first query ran in 347 seconds; the second one in 374 seconds. > Conclusion: There is no significant overhead in the ts_rank function > itself. It's slow because ts_rank has to read in random order 40 000 > ts_vector stored in TOAST table. The slow execution time looks like > a direct consequence of storing ts_vector in TOAST table... > > > :( The only solution I see is to store enough information for ranking > in index. > > Is it the expected behavior? How can I improve that? >
Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
According to Oleg in a previous discussion, ts_rank does not use index because index does not store enough information for ranking: http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php On Sat, Feb 18, 2012 at 12:39, Andrey Chursin wrote: > Hello > > I have two unrelated questions about fts function ts_rank: > > 1) I've created GiST index on column with fts vector, but query > SELECT * FROM table ORDER BY ts_rank(field, :query) LIMIT 20 > is perfomed with sequential table scan. Index was created on field > column. Does it mean FTS indexes does not support order by ranking? Or > I need somehow to create separated index for ranking? > > 2) I have a misunderstanding with proximity ranking work. Given two > vectors 'a:1 b:2' and 'a:1 b:1000', i am measuring ts_rank(vector, 'a' > | 'b'). And it is equal! But when i am replacing query with & > operator, e.g. asking for ts_rank(vector, 'a' & 'b') i am getting > different numbers. Why do I get proximity ranking only for AND fts > queries? This is a problem as far as to_tsquery produces OR queries, > so i need self-written postprocessing of query to replace OR with AND. > > -- > Regards, > Andrey > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Nicolas Grilly Garden / Vocation City +33 1 45 72 48 78 - office +33 6 03 00 25 34 - mobile www.gardentechno.com - Développement web & reporting / *Web development & data analytics* www.vocationcity.com - Plateforme de recrutement sur le web / *Web recruitment platform*
Re: [GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)
On Wed, Jul 13, 2011 at 17:36, Oleg Bartunov wrote:
> I didn't notice, reading 40K tuples in random order takes a long time and
> this
> is a problem of any database. Can you measure time to read all documents
> found ?
As you asked, I measured the time required to read all documents.
For reference, after having dropped the operating system cache, my
machine can read a 1 GB file in 20 seconds, that is 50 MB / second.
Here are the stats for table posts_100:
Table size: 117 MB
TOAST table size: 8356 MB
Index size: 1720 MB
I forced PostgreSQL to read all documents using the following query,
which doesn't involve ts_rank:
explain analyze select sum(length(document_vector)) from posts_100;
Aggregate (cost=27472.52..27472.53 rows=1 width=18) (actual
time=346952.556..346952.557 rows=1 loops=1)
-> Seq Scan on posts_100 (cost=0.00..24975.01 rows=999001
width=18) (actual time=0.023..1793.523 rows=999001 loops=1)
Total runtime: 346952.595 ms
Then I ran a similar query that involves ts_rank:
explain analyze select sum(ts_rank_cd(document_vector,
to_tsquery('english', 'crare'), 32)) from posts_100
Aggregate (cost=27472.52..27472.53 rows=1 width=18) (actual
time=373713.957..373713.958 rows=1 loops=1)
-> Seq Scan on posts_100 (cost=0.00..24975.01 rows=999001
width=18) (actual time=20.045..1847.897 rows=999001 loops=1)
Total runtime: 373714.031 ms
The first query ran in 347 seconds; the second one in 374 seconds.
Conclusion: There is no significant overhead in the ts_rank function
itself. It's slow because ts_rank has to read in random order 40 000
ts_vector stored in TOAST table. The slow execution time looks like
a direct consequence of storing ts_vector in TOAST table...
> :( The only solution I see is to store enough information for ranking in
> index.
Is it the expected behavior? How can I improve that?
Thanks,
Nicolas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)
Hi Oleg and all,
On Wed, Jul 13, 2011 at 08:16, Oleg Bartunov wrote:
> there is problem with estimating of cost scanning gin index in < 9.1
> versions,
> so you can set enable_seqscan=off;
> or try 9.1 which beta3 now.
I re-ran my queries using enable seqscan=off.
Now the first query, without ts_rank, uses the GIN index:
set enable_seqscan=off;
explain analyze select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
limit 50;
Limit (cost=42290.12..42306.31 rows=50 width=4) (actual
time=16.259..16.412 rows=50 loops=1)
-> Bitmap Heap Scan on posts_100 (cost=42290.12..57877.02
rows=48152 width=4) (actual time=16.256..16.344 rows=50 loops=1)
Recheck Cond: ('''crare'''::tsquery @@ document_vector)
-> Bitmap Index Scan on index_posts_documents_100
(cost=0.00..42278.08 rows=48152 width=0) (actual time=13.265..13.265
rows=49951 loops=1)
Index Cond: ('''crare'''::tsquery @@ document_vector)
Total runtime: 16.484 ms
But the second query, the one that uses ts_rank, is still very slow...
Any idea why? Is ts_rank efficient enough to find the best 50 matches
among 50 000 documents?
set enable_seqscan=off;
explain analyze select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
order by ts_rank_cd(document_vector, to_tsquery('english', 'crare'), 32) desc
limit 50;
Limit (cost=59596.98..59597.10 rows=50 width=22) (actual
time=296212.052..296212.257 rows=50 loops=1)
-> Sort (cost=59596.98..59717.36 rows=48152 width=22) (actual
time=296186.928..296187.007 rows=50 loops=1)"
Sort Key: (ts_rank_cd(document_vector, '''crare'''::tsquery, 32))
Sort Method: top-N heapsort Memory: 27kB
-> Bitmap Heap Scan on posts_100
(cost=42290.12..57997.40 rows=48152 width=22) (actual
time=70.861..296059.515 rows=49951 loops=1)
Recheck Cond: ('''crare'''::tsquery @@ document_vector)
-> Bitmap Index Scan on index_posts_documents_100
(cost=0.00..42278.08 rows=48152 width=0) (actual time=24.922..24.922
rows=49951 loops=1)
Index Cond: ('''crare'''::tsquery @@ document_vector)
Total runtime: 296220.493 ms
>> By the way, does ts_rank is supposed to use a GIN index when it's
>> available?
>
> no, I see no benefit :)
Ok. But what is the solution to improve ts_rank execution time? Am I
doing something wrong?
Thanks for your help,
Nicolas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)
On Tue, Jul 12, 2011 at 22:25, Oleg Bartunov wrote: > I don't see your query uses index :) Yes, I know. :) I ran VACUUM ANALYZE and re-ran the query but the output of EXPLAIN ANALYZE stays exactly the same: no index used. Any idea why? By the way, does ts_rank is supposed to use a GIN index when it's available? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)
Hello,
I'm testing PostgreSQL full-text search on a table containing
1.000.000 documents. Document average length is 5.700 chars.
Performance is good and very similar to what I can get with Xapian if
I don't use ts_rank. But response time collapses if I use ts_rank to
select the 50 best matching documents.
This is the table and index definition:
create table posts_100 (
id serial primary key,
document_vector tsvector
);
create index index_posts_documents_100 ON posts_100 USING
gin(document_vector);
This is the query without ts_rank (the word 'crare' matches 5 % of documents):
select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
limit 50
Limit (cost=0.00..27.93 rows=50 width=4) (actual time=0.303..12.559
rows=50 loops=1)
Output: id
-> Seq Scan on posts_100 (cost=0.00..27472.51 rows=49184
width=4) (actual time=0.299..12.451 rows=50 loops=1)
Output: id
Filter: ('''crare'''::tsquery @@ document_vector)
Total runtime: 12.642 ms
Now, this is the query using ts_rank:
select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
order by ts_rank_cd(document_vector, to_tsquery('english', 'crare'), 32) desc
limit 50
Limit (cost=29229.33..29229.45 rows=50 width=22) (actual
time=355516.233..355516.339 rows=50 loops=1)
Output: id
-> Sort (cost=29229.33..29352.29 rows=49184 width=22) (actual
time=355516.230..355516.268 rows=50 loops=1)
Output: id
Sort Key: (ts_rank_cd(document_vector, '''crare'''::tsquery, 32))
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on posts_100 (cost=0.00..27595.47 rows=49184
width=22) (actual time=0.251..355389.367 rows=49951 loops=1)
Output: id
Filter: ('''crare'''::tsquery @@ document_vector)
Total runtime: 355535.063 ms
The ranking is very slow: 140 ranked documents / second on my machine!
I'm afraid this is because ts_rank needs to read document_vector, and
because that column is stored in TOAST table, it triggers a random
access for each matching row. Am I correct? Is it the expected
behavior? Is there a way to reduce the execution time?
I use PostgreSQL 8.4 with shared_buffers = 256 MB, work_mem = 256 MB.
Thanks for your help and advice.
--
Nicolas Grilly
Garden
+33 1 45 72 48 78 - office
+33 6 03 00 25 34 - mobile
www.gardentechno.com - Développement web & reporting / Web development
& data analytics
www.vocationcity.com - Plateforme de recrutement sur le web / Web
recruitment platform
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why "copy ... from stdio" does not return immediately when reading invalid data?
I have analyzed the PostgreSQL protocol using Wireshark (an open source packet analyzer), and I observed that the PostgreSQL backend, while doing a COPY ... FROM STDIN, reports errors as soon as possible (especially errors related to invalid data). Therefore, the "late" reporting of errors while doing a COPY ... FROM STDIN is not a limitation of the underlying protocol; it is a limitation (or a design choice) of the libpq library. It looks like this is a well known issue because it is listed on the todo list: http://wiki.postgresql.org/wiki/Todo#COPY And was discussed before: http://archives.postgresql.org/pgsql-hackers/2008-04/msg01169.php Do you think it is possible to change that behavior, or work around it? While reading libpq source code, I noticed the function pqParseInput3 (file fe-protocol3.c) ignores error responses while the connection is in PGASYNC_COPY_IN state. Maybe we can make a special case for the COPY FROM subprotocol and handle errors early, in order to make them available to PQgetResult? Is is feasible in a simple way or is it a bad idea? Regards, Nicolas Grilly On Wed, Feb 2, 2011 at 20:06, John R Pierce wrote: > On 02/02/11 10:20 AM, Nicolas Grilly wrote: > >> Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send >> gigabytes of data with just one "copy ... from stdio" query, and is there a >> way to be notified of a potential error before calling PQputCopyEnd? Or do I >> have to send my data in small chunks (for example batch of 1 rows), >> issue a PQputCopyEnd, check for errors, and continue with the next chunk? >> > > I would batch the data, maybe 1000 lines or even 100 lines at a time if > these errors are at all frequent. put the errored batches in an exception > list or something so you can sort them out later. >
[GENERAL] Why "copy ... from stdio" does not return immediately when reading invalid data?
Hello, I am importing gigabytes of data into PostgreSQL, and I don't want to wait 10 minutes just to discover an error in the 10th line of my input file. I tried the command "\copy ... from stdio" in psql and it looks like psql has to read the entire input before returning a potential error, even if the invalid value is in one of the first rows. Is it a limitation of PostgreSQL protocol, of the library lipq, or of the tool psql? Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send gigabytes of data with just one "copy ... from stdio" query, and is there a way to be notified of a potential error before calling PQputCopyEnd? Or do I have to send my data in small chunks (for example batch of 1 rows), issue a PQputCopyEnd, check for errors, and continue with the next chunk? Thanks for your help and advice. Regards, Nicolas Grilly
[GENERAL] Using the copy protocol, is there a way to be notified of a potential error before sending?
Is the copy protocol (aka PQputCopyData and PQputCopyEnd) designed to send data during many minutes, and is there a way to be notified of a potential error (for example an invalid value in one column) before sending PQputCopyEnd? Or do I have to send my data in small chunks (for example batch of 1 rows), issue a PQputCopyEnd, check for errors, and continue with the next chunk? I tried the command "\copy ... from stdio" in psql and it looks like psql has to read the entire input before returning an error, even if the invalid value is in one of the first sent rows. Thanks for your help and advice. Regards, Nicolas Grilly
