pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtsearch2 question (was: Poor performance on seq scan)
2+ messages / 2 participants
[nested] [flat]
* tsearch2 question (was: Poor performance on seq scan)
@ 2006-09-12 17:01 Laszlo Nagy <gandalf@designaproduct.biz>
0 siblings, 1 reply; 2+ messages in thread
From: Laszlo Nagy @ 2006-09-12 17:01 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
> Only if the index is capable of disgorging the original value of the
> indexed column, a fact not in evidence in general (counterexample:
> polygons indexed by their bounding boxes in an r-tree). But yeah,
> it's interesting to think about applying filters at the index fetch
> step for index types that can hand back full values. This has been
> discussed before --- I think we had gotten as far as speculating about
> doing joins with just index values. See eg here:
> http://archives.postgresql.org/pgsql-hackers/2004-05/msg00944.php
> A lot of the low-level concerns have already been dealt with in order to
> support bitmap indexscans, but applying non-indexable conditions before
> fetching from the heap is still not done.
>
To overcome this problem, I created a smaller "shadow" table:
CREATE TABLE product_search
(
id int8 NOT NULL,
name_desc text,
CONSTRAINT pk_product_search PRIMARY KEY (id)
);
insert into product_search
select
id,
name || ' ' || coalesce(description,'')
from product;
Obviously, this is almost like an index, but I need to maintain it
manually. I'm able to search with
zeusd1=> explain analyze select id from product_search where name_desc
like '%Mug%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on product_search (cost=0.00..54693.34 rows=36487 width=8)
(actual time=20.036..2541.971 rows=91399 loops=1)
Filter: (name_desc ~~ '%Mug%'::text)
Total runtime: 2581.272 ms
(3 rows)
The total runtime remains below 3 sec in all cases. Of course I still
need to join the main table to the result:
explain analyze select s.id,p.name from product_search s inner join
product p on (p.id = s.id) where s.name_desc like '%Tiffany%'
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..55042.84 rows=58 width=40) (actual
time=164.437..3982.610 rows=117 loops=1)
-> Seq Scan on product_search s (cost=0.00..54693.34 rows=58
width=8) (actual time=103.651..2717.914 rows=117 loops=1)
Filter: (name_desc ~~ '%Tiffany%'::text)
-> Index Scan using pk_product_id on product p (cost=0.00..6.01
rows=1 width=40) (actual time=10.793..10.796 rows=1 loops=117)
Index Cond: (p.id = "outer".id)
Total runtime: 4007.283 ms
(6 rows)
Took 4 seconds. Awesome! With the original table, it used to be one or
two minutes!
Now you can ask, why am I not using tsearch2 for this? Here is answer:
CREATE TABLE product_search
(
id int8 NOT NULL,
ts_name_desc tsvector,
CONSTRAINT pk_product_search PRIMARY KEY (id)
);
insert into product_search
select
id,
to_tsvector(name || ' ' coalesce(description,''))
from product;
CREATE INDEX idx_product_search_ts_name_desc ON product_search USING
gist (ts_name_desc);
VACUUM product_search;
zeusd1=> explain analyze select id from product_search where
ts_name_desc @@ to_tsquery('mug');
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on product_search (cost=25.19..3378.20 rows=912
width=8) (actual time=954.669..13112.009 rows=91434 loops=1)
Filter: (ts_name_desc @@ '''mug'''::tsquery)
-> Bitmap Index Scan on idx_product_search_ts_name_desc
(cost=0.00..25.19 rows=912 width=0) (actual time=932.455..932.455
rows=91436 loops=1)
Index Cond: (ts_name_desc @@ '''mug'''::tsquery)
Total runtime: 13155.724 ms
(5 rows)
zeusd1=> explain analyze select id from product_search where
ts_name_desc @@ to_tsquery('tiffany');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on product_search (cost=25.19..3378.20 rows=912
width=8) (actual time=13151.725..13639.112 rows=76 loops=1)
Filter: (ts_name_desc @@ '''tiffani'''::tsquery)
-> Bitmap Index Scan on idx_product_search_ts_name_desc
(cost=0.00..25.19 rows=912 width=0) (actual time=13123.705..13123.705
rows=81 loops=1)
Index Cond: (ts_name_desc @@ '''tiffani'''::tsquery)
Total runtime: 13639.478 ms
(5 rows)
At least 13 seconds, and the main table is not joined yet. Can anybody
explain to me, why the seq scan is faster than the bitmap index? In the
last example there were only 81 rows returned, but it took more than 13
seconds. :( Even if the whole table can be cached into memory (which
isn't the case), the bitmap index should be much faster. Probably there
is a big problem with my schema but I cannot find it. What am I doing wrong?
Thanks,
Laszlo
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: tsearch2 question (was: Poor performance on seq
@ 2006-09-12 18:36 Luke Lonergan <llonergan@greenplum.com>
parent: Laszlo Nagy <gandalf@designaproduct.biz>
0 siblings, 0 replies; 2+ messages in thread
From: Luke Lonergan @ 2006-09-12 18:36 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@designaproduct.biz>; pgsql-performance
Lazlo,
On 9/12/06 10:01 AM, "Laszlo Nagy" <gandalf@designaproduct.biz> wrote:
> zeusd1=> explain analyze select id from product_search where name_desc
> like '%Mug%';
> QUERY PLAN
> ------------------------------------------------------------------------------
> ------------------------------------------
> Seq Scan on product_search (cost=0.00..54693.34 rows=36487 width=8)
> (actual time=20.036..2541.971 rows=91399 loops=1)
> Filter: (name_desc ~~ '%Mug%'::text)
> Total runtime: 2581.272 ms
> (3 rows)
>
> The total runtime remains below 3 sec in all cases.
By creating a table with only the name field you are searching, you have
just reduced the size of rows so that they fit in memory. That is why your
query runs faster.
If your searched data doesn't grow, this is fine. If it does, you will need
to fix your disk drive OS problem.
- Luke
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-09-12 18:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-12 17:01 tsearch2 question (was: Poor performance on seq scan) Laszlo Nagy <gandalf@designaproduct.biz>
2006-09-12 18:36 ` Re: tsearch2 question (was: Poor performance on seq Luke Lonergan <llonergan@greenplum.com>
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