| Index Cond: ((subscriber_id = 1234) AND (_type = 'book'::text)) | | Recheck Cond: ((subscriber_id = 1234) AND (_type = 'book'::text)) |
then, the previous query will execute using this index
If you have very many _type, or you want your query to work with any possible _type (and not two specifically chosen) your second choice would be to have the following index: CREATE INDEX idx_subscriber_type ON selected_media (subscriber_id, _type) | -> Index Only Scan using idx_movies on selected_media selected_media_1 (cost=0.12.4.14 rows=1 width=0) (actual time=0.001.0.001 rows=0 loops=1) | This will give you a query plan doing two index only scans most of the time. Nullif( (select count(*) from selected_media where _type='movie' In SQL you probably need to know in advance the number of columns to show. Nullif( (select count(*) from selected_media where _type='book' postgresql dbeaver Share Improve this question Follow asked yesterday Marcio Lino 381 2 15 That's called 'pivoting'. If you need the fastest possible speed, and depending on the number of _types, and the number of elements of each type, you could use some partial indexes: CREATE INDEX idx_books ON selected_media (subscriber_id) WHERE _type='book' ĬREATE INDEX idx_movies ON selected_media (subscriber_id) WHERE _type='movie' Īnd then perform your query as two subqueries: SELECT ) instead of SUM, and, in latest versions of PostgreSQL, you can use a FILTER, instead of CASE: SELECTĬount(*) FILTER (WHERE _type = 'book') AS books_count,Ĭount(*) FILTER (WHERE _type = 'movie') AS movies_count
If you don't want to have a NULL value when the result is zero, but another one, you can use: SELECTĮLSE (another_value_or_expression_for_books)ĮLSE (another_value_or_expression_for_movies) SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count SUM(CASE WHEN _type = 'book' THEN 1 END) AS books_count, So the estimates should be good unless you had major changes very recently.Then do it slightly different, use the (more or less) complementary of COALESCE, which is NULLIF: SELECT
#Postgresql count manual#
If you are running the autovacuum daemon as is the default for modern PostgreSQL, ANALYZE is run automatically, too (except for temporary tables which need manual attention). There are three separate approaches to pattern matching provided by PostgreSQL: the traditional SQL LIKE operator, the more recent SIMILAR TO operator (added in SQL:1999), and POSIX -style regular expressions. If you didn't ANALYZE recently (after last changes), the estimates will be off more or less. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. This is only an estimate used by the planner. Quoting the manual for Postgres 13 on pg_class.reltuples: Number of live rows in the table. The cast to bigint formats the real number nicely, especially for big counts. SELECT reltuples :: bigint AS estimate FROM pg_class WHERE oid = 'schema_name.table_name' :: regclass
0 kommentar(er)
